Search code examples
sqlsql-serveroracle-databasestandardsplatform-independent

Platform Neutral SQL Statement for INSTR or CHARINDEX

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.

Solution

  • 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.