MySQL SQL SELECT 某个字符后的所有内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19495427/
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
SQL SELECT everything after a certain character
提问by popkutt
I need to extract everything after the last '=' (http://www.domain.com?query=blablabla- > blablabla) but this query returns the entire strings. Where did I go wrong in here:
我需要在最后一个 '=' ( http://www.domain.com?query=blablabla-> blablabla )之后提取所有内容，但此查询返回整个字符串。我在这里哪里出错了：
SELECT RIGHT(supplier_reference, CHAR_LENGTH(supplier_reference) - SUBSTRING('=', supplier_reference))
FROM ps_product
回答by virus
select SUBSTRING_INDEX(supplier_reference,'=',-1) from ps_product;
Please use http://www.w3resource.com/mysql/string-functions/mysql-substring_index-function.phpfor further reference.
请使用http://www.w3resource.com/mysql/string-functions/mysql-substring_index-function.php作进一步参考。
回答by BWS
Try this (it should work if there are multiple '=' characters in the string):
试试这个（如果字符串中有多个 '=' 字符，它应该可以工作）：
SELECT RIGHT(supplier_reference, (CHARINDEX('=',REVERSE(supplier_reference),0))-1) FROM ps_product
回答by Mohsin
Try this in MySQL.
在 MySQL 中试试这个。
right(field,((CHAR_LENGTH(field))-(InStr(field,','))))
回答by Ludo
In MySQL, this works if there are multiple '=' characters in the string
在 MySQL 中，如果字符串中有多个 '=' 字符，则此方法有效
SUBSTRING(supplier_reference FROM (LOCATE('=',supplier_reference)+1))
It returns the substring after(+1) having found the the first =
它在 (+1) 找到第一个后返回子字符串 =
回答by Rosu Flavius
I've been working on something similar and after a few tries and fails came up with this:
我一直在做类似的事情，经过几次尝试和失败，我想出了这个：
Example: STRING-TO-TEST-ON = 'ab,cd,ef,gh'
示例： STRING-TO-TEST-ON = 'ab,cd,ef,gh'
I wanted to extract everything after the last occurrence of "," (comma) from the string... resulting in "gh".
我想从字符串中最后一次出现“，”（逗号）之后提取所有内容......导致“gh”。
My query is:
我的查询是：
SELECT SUBSTR('ab,cd,ef,gh' FROM (LENGTH('ab,cd,ef,gh') - (LOCATE(",",REVERSE('ab,cd,ef,gh'))-1)+1)) AS `wantedString`
Now let me try and explain what I did ...
现在让我试着解释一下我做了什么......
I had to find the position of the last "," from the string and to calculate the wantedString length, using
LOCATE(",",REVERSE('ab,cd,ef,gh'))-1by reversing the initial string I actually had to find the first occurrence of the "," in the string ... which wasn't hard to do ... and then -1 to actually find the string length without the ",".calculate the position of my wantedString by subtracting the string length I've calculated at 1st step from the initial string length:
LENGTH('ab,cd,ef,gh') - (LOCATE(",",REVERSE('ab,cd,ef,gh'))-1)+1
我必须从字符串中找到最后一个“,”的位置并计算想要的字符串长度，
LOCATE(",",REVERSE('ab,cd,ef,gh'))-1通过反转初始字符串我实际上必须找到字符串中第一次出现的“，”......这不是' t 很难做到......然后 -1 实际找到没有“，”的字符串长度。通过从初始字符串长度中减去我在第一步计算的字符串长度来计算我想要的字符串的位置：
LENGTH('ab,cd,ef,gh') - (LOCATE(",",REVERSE('ab,cd,ef,gh'))-1)+1
I have (+1) because I actually need the string position after the last "," .. and not containing the ",". Hope it makes sense.
我有（+1），因为我实际上需要最后一个“，”之后的字符串位置......并且不包含“，”。希望这是有道理的。
- all it remain to do is running a SUBSTR on my initial string FROM the calculated position.
- 剩下要做的就是在我的初始字符串上从计算位置运行一个 SUBSTR。
I haven't tested the query on large strings so I do not know how slow it is. So if someone actually tests it on a large string I would very happy to know the results.
我没有在大字符串上测试查询，所以我不知道它有多慢。所以如果有人真的在一个大字符串上测试它，我会很高兴知道结果。
回答by Jacob Morris
For SQL Management studio I used a variation of BWS' answer. This gets the data to the right of '=', or NULL if the symbol doesn't exist:
对于 SQL 管理工作室，我使用了 BWS 答案的变体。这将获取 '=' 右侧的数据，如果符号不存在，则为 NULL：
CASE WHEN (RIGHT(supplier_reference, CASE WHEN (CHARINDEX('=',supplier_reference,0)) = 0 THEN
0 ELSE CHARINDEX('=', supplier_reference) -1 END)) <> '' THEN (RIGHT(supplier_reference, CASE WHEN (CHARINDEX('=',supplier_reference,0)) = 0 THEN
0 ELSE CHARINDEX('=', supplier_reference) -1 END)) ELSE NULL END
