如何对 MySQL 表列进行子串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12771311/
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
How to substring a MySQL table column
提问by Rakesh
I want to select a field from table and substring it.
我想从表中选择一个字段并将其子字符串化。
For example:
例如：
VAN1031 --> 1031
I tried this, but is improper syntax:
我试过这个，但语法不正确：
SELECT SUBSTR(R.regnumber,3,3) from registration R
How can this be done?
如何才能做到这一点？
采纳答案by Mark Byers
You don't need the third argument (length) if you want to select all the characters to the right of a specific index:
如果要选择特定索引右侧的所有字符，则不需要第三个参数（长度）：
SELECT SUBSTR(R.regnumber, 4)
FROM registration AS R
I also changed the start index to 4 because in SQL strings are 1-indexed and not 0-indexed as they are in many popular programming languages.
我还将起始索引更改为 4，因为在 SQL 中字符串是 1-indexed 而不是 0-indexed，因为它们在许多流行的编程语言中都是如此。
回答by Anil Limbani
You can use:
您可以使用：
SUBSTR(string,position)
SUBSTR(string,position,length)
SUBSTRING_INDEX(string, delimiter, count)
Examples:
例子：
command prints
------------------------------------------- -----------
select substr("abcd", 1, 1) #a
select substr("abcd", 1, 2) #ab
select substr("abcd", 2, 1) #b
select substr("abcd", 2, 2) #bc
select substr("abcd", -2, 1) #c
select substr("abcd", -2, 2) #cd
select substring_index('ababab', 'b', 1); #a
select substring_index('ababab', 'b', 2); #aba
select substring_index('ababab', 'b', 3); #ababa
select substring_index('ababab', 'b', -1); #
select substring_index('ababab', 'b', -2); #ab
select substring_index('ababab', 'b', -3); #abab
select substr("abcd", 2) #bcd
select substr("abcd", 3) #cd
select substr("abcd", 4) #d
select substr("abcd", -2) #cd
select substr("abcd", -3) #bcd
select substr("abcd", -4) #abcd
From this link.
从这个链接。
回答by Taryn
You can use SUBSTRING():
您可以使用SUBSTRING()：
select substring(col1, 4)
from table1
See SQL Fiddle with Demo. So your query would be:
请参阅SQL Fiddle with Demo。所以你的查询是：
SELECT substring(R.regnumber,4)
from registration R
Of if you want to specify the number of characters to return:
如果要指定要返回的字符数：
select substring(col1, 4, 4)
from table1
回答by Ruberandinda Patience
I noticed that mysql index starts from 1 instead of zero as many programming languages did.
我注意到 mysql 索引从 1 开始，而不是像许多编程语言那样从 0 开始。
SELECT SUBSTRING(R.regNumber,1,3) FROM registration AS R
Returns VAN and
返回 VAN 和
SELECT SUBSTRING(R.regNumber,4) FROM registration AS R
Returns the remaining part of the string as 1031
将字符串的剩余部分返回为 1031
回答by Marcelo Agimóvel
Sometimes you need to catch the column without some last characters. For example, I have:
有时您需要捕获没有一些最后字符的列。例如，我有：
This is a string
Let's say that for some reason I want column without last 6 characters:
假设出于某种原因，我想要没有最后 6 个字符的列：
This is a
We can do (using @bluefeet 's answer and LENGHT):
我们可以做（使用@bluefeet 的答案和长度）：
select substring(col1, 1,LENGTH(col1)-7)
from table1
It was only an example, but you got the idea, I'm using to fix a wrong database import.
这只是一个示例，但您明白了，我正在使用它来修复错误的数据库导入。
回答by Mohsen B
SELECT substring(R.regnumber FROM 4) FROM registration AS R;
and if you want to take the part as an integer not a string you can write:
如果您想将该部分作为整数而不是字符串，您可以编写：
SELECT CAST(substring(R.regnumber FROM 4) AS UNSIGNED) FROM registration as R;
