SQL 查询子串形成
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3220584/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
query for substring formation
提问by abhinav singh
I want to take the 01part of a string abcd_01using SQL. What should be the query for this, where the length before the _varies? That is, it may be abcde_01or ab_01. Basically, I want part after the _.
我想使用 SQL 获取01字符串的一部分abcd_01。这个查询应该是什么，前面的长度_不同？也就是说，它可能是abcde_01或ab_01。基本上，我想在_.
回答by OMG Ponies
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.
这是 SQL 和各种扩展之间如何具有相似功能的示例之一，但它们的差异足以让您无法保证所有数据库之间的可移植性。
The SUBSTRING keyword, using PostgreSQL syntax (no mention of pattern matching) is ANSI-99. Why this took them so long, I dunno...
使用 PostgreSQL 语法（未提及模式匹配）的SUBSTRING 关键字是 ANSI-99。为什么这花了他们这么长时间，我不知道......
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 SUBSTRdoes allow you to specify a negative number in order to process the string in reverse (end towards the start).
Oracle 没有 RIGHT 函数，它实际上只是子字符串函数的包装器。但是Oracle 的 SUBSTR确实允许您指定一个负数，以便反向处理字符串（从头到尾）。
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...
为简洁起见，RIGHT 是理想的。但是为了便携性，SUBSTRING 是更好的选择......
MySQL
MySQL
Like SQL Server, three options - SUBSTR, SUBSTRING, and RIGHT:
像 SQL Server 一样，三个选项 - SUBSTR、SUBSTRING和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
PostgreSQL only has SUBSTRING:
PostgreSQL 只有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
SQLite only supports SUBSTR:
SQLite 仅支持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.
如果可用，请使用 RIGHT，而如果需要将查询移植到其他数据库，则 SUBSTR/SUBSTRING 会更好，这样其他人就可以清楚地看到正在发生的事情，并且应该更容易找到等效的功能。
回答by gbn
If it's always the last 2 characters then use RIGHT(MyString, 2)in most SQL dialects
如果它总是最后 2 个字符，那么RIGHT(MyString, 2)在大多数 SQL 方言中使用
回答by HungryCoder
to get 01 from abcd_01 you should write this way (assuming column name is col1)
要从 abcd_01 获取 01 你应该这样写（假设列名是 col1）
SELECT substring(col1,-2) FROM TABLE
从表中选择子字符串（col1，-2）
this will give you last two chars.
这会给你最后两个字符。
回答by user3746307
select substring(names,charindex('_',names)+1,len(names)-charindex('_',names)) from test
