Friday, July 25, 2014

SQL Server RIGHT, LEFT, SUBSTRING vs. Oracle SUBSTR

I've been doing a lot of data analysis in both SQL Server and Oracle lately. Thus, it seemed like a good time for another SQL Server vs. Oracle differences post.

If you've first worked with SQL Server and then Oracle, you've probably looked for either the RIGHT or LEFT function in Oracle and been unable to find it. Instead Oracle has a powerful SUBSTR function that covers RIGHT, LEFT, and SUBSTRING from SQL Server. Below are some examples.

1) Get right four characters from a string.

SQL Server
Query: SELECT RIGHT('My favorite year is 1998', 4);
Result: 1998

Oracle
Query: SELECT SUBSTR('My favorite year is 1998', -4) FROM DUAL;

Note: Oracle allows a negative starting position with SUBSTR, thus allowing it to mimic the SQL Server RIGHT function.

2) Get left 18 characters from a string.

SQL Server
Query: SELECT LEFT('Bricktown is super and fun', 18);
Result: Bricktown is super

Oracle
Query: SELECT SUBSTR('Bricktown is super and fun', 1, 18) FROM DUAL;
Result: Bricktown is super

3) Get 14 characters starting at character 11

SQL Server
Query: SELECT SUBSTRING('The state Hawaii is warm and sunny', 11, 14);
Result: Hawaii is warm

Oracle: SELECT SUBSTR('The state Hawaii is warm and sunny', 11, 14) FROM DUAL;
Result: Hawaii is warm

Note: SQL Server's SUBSTRING function requires 3 arguments.

There you have it. Both the SQL Server and Oracle functions are pretty easy to use, as long as you can remember the syntax for the database you are using.