使用 SQL 从字段中选择 1 个单词

发布于 2024-11-17 05:26:21 字数 439 浏览 3 评论 0原文

我需要从列中提取“SRN=123”（123 的长度是动态的，SRN= 是一致的）。数据可以是任何东西，没有一致的格式

456 lorem limpsump SRN=123 和一些更多事情3.

我在尝试使用 charindex 来查找 SRN=123 的结束点来获取长度时遇到问题，有帮助吗？

SUBSTRING(t.Instructions, 
                             CharIndex('SRN=', t.Instructions) + 10, 
                             (CHARINDEX('SRN=', t.Instructions )-(CharIndex('SRN=[^0-9.-]', t.Instructions) + 10)))

原文

I need to extract "SRN=123" (the 123 is dynamic in length, SRN= is consistent) from a column. Data could be anything, no consistent format

456 lorem limpsump SRN=123 and some
more things 3.

I'm having trouble trying to use a charindex to find the ending point of SRN=123 to get the length, any help?

SUBSTRING(t.Instructions, 
                             CharIndex('SRN=', t.Instructions) + 10, 
                             (CHARINDEX('SRN=', t.Instructions )-(CharIndex('SRN=[^0-9.-]', t.Instructions) + 10)))

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

旧时浪漫 2024-11-24 05:26:21

select 'SRN='+left(stuff(@S, 1, charindex('SRN=', @S)+3, ''), patindex('%[^0-9]%', stuff(@S, 1, charindex('SRN=', @S)+3, '')+' ')-1)

https://data.stackexchange.com/stackoverflow/q/104003/

select 'SRN='+left(stuff(@S, 1, charindex('SRN=', @S)+3, ''), patindex('%[^0-9]%', stuff(@S, 1, charindex('SRN=', @S)+3, '')+' ')-1)

https://data.stackexchange.com/stackoverflow/q/104003/

回复收藏 0 原文

月野兔 2024-11-24 05:26:21

做出一个巨大的假设，即您需要的字符串末尾有一个空格。

declare @str as varchar(100)
set @str='456 lorem limpsump SRN=123 and some more things 3'

--for testing, find the starting point
select charindex('SRN=',@str)

--for testing, find the ending point
select charindex(' ',@str,charindex('SRN=',@str))

--find your substring 
select substring(@str,charindex('SRN=',@str),charindex(' ',@str,charindex('SRN=',@str))-charindex('SRN=',@str))

Making a GIANT assumption that there is a space at the end of the string you need.

declare @str as varchar(100)
set @str='456 lorem limpsump SRN=123 and some more things 3'

--for testing, find the starting point
select charindex('SRN=',@str)

--for testing, find the ending point
select charindex(' ',@str,charindex('SRN=',@str))

--find your substring 
select substring(@str,charindex('SRN=',@str),charindex(' ',@str,charindex('SRN=',@str))-charindex('SRN=',@str))

回复收藏 0 原文

另类 2024-11-24 05:26:21

这有效

with t as(
select '456 lorem limpsump SRN=12378 and some more things 3.' as col )

select substring(col,charindex('SRN=',col)+4,
charindex(' ',col,charindex('SRN=',col))-charindex('SRN=',col)-4)
from t

This works

with t as(
select '456 lorem limpsump SRN=12378 and some more things 3.' as col )

select substring(col,charindex('SRN=',col)+4,
charindex(' ',col,charindex('SRN=',col))-charindex('SRN=',col)-4)
from t

回复收藏 0 原文

仙气飘飘 2024-11-24 05:26:21

抱歉...错过了您只是想要 SRN=12345

DECLARE @STRING VARCHAR(1000)
SELECT @STRING = '456 lorem limpsump SRN=123456 and some more things 3.'
SELECT SUBSTRING(@STRING, CHARINDEX('SRN=', @string, 0), CHARINDEX(' ', @string, CHARINDEX('SRN=', @string, 0)) - CHARINDEX('SRN=', @string, 0))

Sorry... missed that you were just wanting the SRN=12345

DECLARE @STRING VARCHAR(1000)
SELECT @STRING = '456 lorem limpsump SRN=123456 and some more things 3.'
SELECT SUBSTRING(@STRING, CHARINDEX('SRN=', @string, 0), CHARINDEX(' ', @string, CHARINDEX('SRN=', @string, 0)) - CHARINDEX('SRN=', @string, 0))

回复收藏 0 原文

~没有更多了~