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
ab_01. Essentially, I would like part following the
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.
SELECT SUBSTR('abcd_01', -2) FROM DUAL
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).
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...
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.