基于模式的 SQL Server 字符串提取 [英] SQL Server String extract based on pattern
问题描述
我有以下格式的字符串数据:
I have string data in the following format:
MODELNUMBER=Z12345&HELLOWORLD=WY554&GADTYPE=PLA&ID=Z-12345
/DTYPE=PLA&ID=S-10758&UN_JTT_REDIRECT=UN_JTT_IOSV
并且需要根据两个条件提取ID
and need to extract IDs based on two conditions
- 从模式开始
&ID=
结束到最后一个字符或
- Starting after a pattern
&ID=
Ending till the last character or
如果遇到&
就停在那里.
所以在上面的例子中我使用了以下代码:
So in the above example I'm using the following code:
SUBSTRING(MyCol,(PATINDEX('%&id=%',[MyCol])+4),(LEN(MyCol) - PATINDEX('%&id%',[MyCol])))
本质上查看模式 &id=% 并在此之后提取字符串直到行尾.有人会建议如何处理逻辑的后半部分..
Essentially looking the pattern &id=% and extract string after that till end of the line. Would anyone advise on how to handle the later part of the logic ..
我目前的结果是
Z-12345
Z-12345&UN_JTT_REDIRECT=UN_JTT_IOSV
我需要的是
Z-12345
Z-12345
推荐答案
试试这个
SUBSTRING(MyCol, (PATINDEX('%[A-Z]-[0-9][0-9][0-9][0-9][0-9]%',[MyCol])),7)
如果您遇到性能问题,请添加下面的 where 子句
if you run into performance issues add the where clause below
-- from Mytable
WHERE [MyCol] like '%[A-Z]-[0-9][0-9][0-9][0-9][0-9]%'
也许不是最优雅的解决方案,但它对我有用.
maybe not the most elegant solution but it works for me.
这篇关于基于模式的 SQL Server 字符串提取的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!