Platform Neutral SQL Statement for INSTR or CHARINDEX

Question

I have a problem writing a SQL statement for both Oracle and MS SQL server. I'd like to write the SQL so that it works in both. I don't want to have to mess around with setting a database type variable and switching

I have a column of course names that have values like this:

9RA923.2008W

1223.200710

P0033330.200901

I want to select everything right of the ".".

In oracle I am using this:

SELECT substr(bom_course_id, instr(bom_course_id, '.')+1) FROM ...

In MSSQL Server I could use this:

SELECT SUBSTRING(bom_course_id, CHARINDEX('.', bom_course_id)+1 ) FROM ...

Does anyone have a clever way that I can select the last characters after the dot, using the same SQL statement in either Oracle or MS SQL.

Unfortunately, I won't know how many characters there will be before or after the "." It's not completely numeric either, I can't count on only numbers.

I really wish there was an SQL standard.

Answer 1

Can you cast it to a numeric and then take just the decimal part?

Answer 2

If your regional settings are same on both databases, you may use

SELECT DISTINCT CAST(CAST(bom_course_id AS FLOAT) AS INTEGER)

Answer 3

You could write an Oracle function named RIGHT which is doing the same like the SQL Server one and then use it in your SQL.

Here is just an example of an implementation.

EDIT: After reading your comment, a RIGHT function wouldn't be really helpful in your situation. Then you would need to map the according SQL Server functions to Oracle.

Answer 4

Have you considered just hiding the select in a database view ? From the application point of view, it will just be a plain, vanilla SELECT with no functions and all the database-specific stuff remains in the database.

May not suit everyone, but its an idea.

Answer 5

Looking at this link, I do not see a standard way to achieve what you want to do.

ORACLE uses: INSTR
SQL SERVER uses: PATINDEX, CHARINDEX

ORACLE uses: SUBSTR
SQL SERVER uses: SUBSTRING

I could not think of a common method to obtain a character position.

A VIEW is probably the easiest way to proceed, or to do it within the client application itself.