query for substring formation query for substring formation sql sql

query for substring formation

This is one of those examples of how there's similar functionality between SQL and the various extensions, but are just different enough that you can not guarantee portability between all databases.

The SUBSTRING keyword, using PostgreSQL syntax (no mention of pattern matching) is ANSI-99. Why this took them so long, I don't know.

The crux of your need is to obtain a substring of the existing column value, so you need to know what the database substring function(s) are called.



Oracle doesn't have a RIGHT function, with is really just a wrapper for the substring function anyway. But Oracle's SUBSTR does allow you to specify a negative number in order to process the string in reverse (end towards the start).

SQL Server

Two options - SUBSTRING, and RIGHT:

SELECT SUBSTRING('abcd_01', LEN('abcd_01') - 1, 2)SELECT RIGHT('abcd_01', 2)

For brevity, RIGHT is ideal. But for portability, SUBSTRING is a better choice...


Like SQL Server, three options - SUBSTR, SUBSTRING, and RIGHT:

SELECT SUBSTR('abcd_01', LENGTH('abcd_01') - 1, 2)SELECT SUBSTRING('abcd_01', LENGTH('abcd_01') - 1, 2)SELECT RIGHT('abcd_01', 2)


PostgreSQL only has SUBSTRING:

 SELECT SUBSTRING('abcd_01' FROM LENGTH('abcd_01')-1 for 2)

...but it does support limited pattern matching, which you can see is not supported elsewhere.


SQLite only supports SUBSTR:

SELECT SUBSTR('abcd_01', LENGTH('abcd_01') - 1, 2)


Use RIGHT if it's available, while SUBSTR/SUBSTRING would be better if there's a need to port the query to other databases so it's explicit to others what is happening and should be easier to find equivalent functionality.

If it's always the last 2 characters then use RIGHT(MyString, 2) in most SQL dialects

to get 01 from abcd_01 you should write this way (assuming column name is col1)

SELECT substring(col1,-2) FROM TABLE

this will give you last two chars.