Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

sql - query for substring formation

I want to take the 01 part of a string abcd_01 using SQL. What should be the query for this, where the length before the _ varies? That is, it may be abcde_01 or ab_01. Basically, I want part after the _.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


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

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...

MySQL


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


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


SQLite only supports SUBSTR:

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

Conclusion


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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...