Subline Formation Request

Question

I want to take 01 part of the string abcd_01 using SQL. What should be the request for this, where the length to _ changes? That is, it can be abcde_01 or ab_01 . Basically, I want the part after _ .

Answer 1

This is one example of how similar functionality is between SQL and various extensions, but it is different enough that you cannot guarantee portability between all databases.

The SUBSTRING keyword using PostgreSQL syntax (without mentioning pattern matching) is ANSI-99 . Why did it take them so long, I don’t know ...

The essence of your need is to get a substring of an existing column value, so you need to know what functions the database substring calls.

Oracle

---

 SELECT SUBSTR('abcd_01', -2) FROM DUAL 

There is no RIGHT function in Oracle, in reality it is just a wrapper for a substring function. But Oracle SUBSTR allows you to specify a negative number to process the string in the reverse order (end to beginning).

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 the best choice ...

MySQL

---

Like SQL Server, the three options are 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

---

PostgreSQL has SUBSTRING :

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

... but it supports the limited pattern matching that you can see is not supported elsewhere.

Sqlite

---

SQLite only supports SUBSTR :

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

Conclusion

---

Use RIGHT if available, while SUBSTR / SUBSTRING would be better if you need to transfer the query to other databases so that it is obvious to others what is happening and it should be easier to find equivalent functionality.

Answer 2

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

Answer 3

to get 01 from abcd_01 you have to write this path (assuming the column name is col1)

SELECT Sub String (col1, -2) FROM TABLE

this will give you the last two characters.

Answer 4

 select substring(names,charindex('_',names)+1,len(names)-charindex('_',names)) from test