SQL SQL如何提取字符串的中间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3678496/
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 how to extract middle of string
提问by user380432
I want to only get the unit number out of this string:
我只想从这个字符串中获取单元号：
'<p>The status for the Unit # 3546 has changed from % to OUTTOVENDOR</p>'
The note is always exactly the same but the unit number changes. How do I extract just the unit number?
音符始终完全相同，但单位编号发生变化。如何仅提取单元号？
Thanks!
谢谢！
采纳答案by Joe Stefanelli
declare @String varchar(500)
set @String = '<p>The status for the Unit # 3546 has changed from % to OUTTOVENDOR</p>'
select SUBSTRING(@String, charindex('# ', @String) + 2, charindex('has changed', @String) - charindex('# ', @String) - 2)
回答by Charles Bretana
try:
尝试：
declare @S VarChar(1000)
Set @S =
'<p>The status for the Unit # 3546 has changed from % to OUTTOVENDOR</p>'
Select Substring( @s,
charIndex('#', @S)+1,
charIndex('has', @S) - 2 - charIndex('#', @S))
回答by Eugene Kuleshov
Something like this should work SUBSTRING(val, 30, CHARINDEX(' ', val, 30)-30)
这样的事情应该工作 SUBSTRING(val, 30, CHARINDEX(' ', val, 30)-30)
回答by bobs
Here's a version that uses PATINDEX. It looks for the first number in a string.
这是一个使用PATINDEX. 它查找字符串中的第一个数字。
declare @MyString varchar(100)
set @MyString = '<p>The status for the Unit # 3546 has changed from % to OUTTOVENDOR</p>'
select PATINDEX('%[0-9]%', @MyString), PATINDEX('%[^0-9]%', SUBSTRING(@MyString, PATINDEX('%[0-9]%', @MyString), len(@MyString)))
select SUBSTRING (@MyString, PATINDEX('%[0-9]%', @MyString),
PATINDEX('%[^0-9]%', SUBSTRING(@MyString, PATINDEX('%[0-9]%', @MyString), len(@MyString))))
回答by Beth
select substring(note, len('<p>The status for the Unit # '),4) from tbl
回答by Chris Cameron-Mills
You could do this any number of ways, use the REPLACE function to strip out either side, use SUBSTRING if you are guaranteed to know index numbers or mix a SUBSTRING with a CHARINDEX.
您可以通过多种方式执行此操作，使用 REPLACE 函数去除任一侧，如果您保证知道索引号或将 SUBSTRING 与 CHARINDEX 混合使用，则使用 SUBSTRING。
