I wish to go ahead and take 01 a part of a string abcd_01 using SQL. What ought to be the query with this, in which the length prior to the _ varies? That's, it might be abcde_01 or ab_01. Essentially, I would like part following the _. Thanks.

This is just one of individuals good examples of methods there's similar functionality between SQL and also the various extensions, but they are just different enough that you could not guarantee portability between all databases.

The SUBSTRING keyword, using PostgreSQL syntax (no mention of pattern matching) is ANSI-99. Why this required them such a long time, I dunno...

The crux of the require is to acquire a substring from the existing column value, so you should know exactly what the database substring function(s) are known as.



Oracle does not possess a RIGHT function, with is actually only a wrapper for that substring function anyway. But Oracle's SUBSTR does permit you to specify an adverse number to be able to process the string backwards (finish for 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 perfect. However for portability, SUBSTRING is a better option...


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 has only SUBSTRING:

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

...however it does support limited pattern matching, which you'll see isn't supported elsewhere.


SQLite only supports SUBSTR:

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


Use RIGHT whether it's available, while SUBSTR/SUBSTRING could be better if there is a have to port the query with other databases therefore it is explicit to others what's happening and really should be simpler to locate equivalent functionality.

Whether it's always the final 2 figures then use RIGHT(MyString, 2) in many SQL dialects

to obtain 01 from abcd_01 you need to write by doing this (presuming column title is col1)

Choose substring(col1,-2) FROM TABLE

this provides you with latter chars.