如何在 SQL Server 中提取子字符串 [英] How to extract substring in SQL Server
本文介绍了如何在 SQL Server 中提取子字符串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个字段类似于 okbogkbogkbokgbokgobkgobkofkgbokfgbo&name=fokdofkd&okfaos
我想从数据中提取name = value
.
如何以有效的方式使用 SQL Server 2008 做到这一点?谢谢
How can I do that using SQL Server 2008 in efficient way? thank you
推荐答案
试试这个
;
With MyTable as
(
SELECT 'okbogkbogkbokgbokgobkgobkofkgbokfgbo&name=fokdofkd&okfaos' FullString
UNION
SELECT 'fkgbokfgbo&name=fokdofkd&okfaos' FullString
UNION
SELECT 'okbogkbogkbokgbok' FullString
),
PatternIndex as
(
Select
FullString + '&' FullString,
CharIndex ('&name=', FullString) + 1 LocationOfEqualSign,
CharIndex ('&', FullString, CharIndex ('&name=', FullString)+1) as NextBreak
from MyTable
),
SplitNameValue AS
(
Select
FullString,
CASE
WHEN NextBreak <> 0 THEN
SubString (FullString, LocationOfEqualSign, NextBreak-LocationOfEqualSign)
ELSE '' END
as NameValuePair
From PatternIndex
)
SELECT *
FROM SplitNameValue
退货
FullString NameValuePair
--------------------------------------------------------- ---------------------------------------------------------
fkgbokfgbo&name=fokdofkd&okfaos name=fokdofkd
okbogkbogkbokgbok
okbogkbogkbokgbokgobkgobkofkgbokfgbo&name=fokdofkd&okfaos name=fokdofkd
(3 row(s) affected)
这篇关于如何在 SQL Server 中提取子字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文