拆分SQL函数，计算字符串长度b/w 2和7个逗号。
拆分SQL函数，计算字符串长度b/w 2和7个逗号。
提问于 2017-06-01 16:54:21
假设我的字符串在它们之间有逗号，我想要找到第二到第七个逗号或(n到n+)之间的字符串的长度。
我在用这个步骤。
CREATE FUNCTION [dbo].[fn_split_1]
(
@sInputList VARCHAR(MAX), -- List of delimited items
@sDelimiter VARCHAR(5) = ',' -- Delimiter that separates items
)
RETURNS @List TABLE (id int,item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
Declare @Count int
SET @Count =1
WHILE CHARINDEX(@sDelimiter, @sInputList, 0) <> 0
BEGIN
SELECT
@sItem = RTRIM(LTRIM(SUBSTRING(@sInputList, 1, CHARINDEX(@sDelimiter, @sInputList, 0) - 1))),
@sInputList = RTRIM(LTRIM(SUBSTRING(@sInputList, CHARINDEX(@sDelimiter, @sInputList, 0) + LEN(@sDelimiter), LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @Count ,@sItem
SET @Count =@Count +1
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @Count ,@sInputList -- Put the last item in
SET @Count =@Count +1
RETURN
END
Select sum(len(item))+(7-2)as'LengthOfChar(b/w 2 and 7 comma)','abc,def,efg,hij,lkm,nop,qrs,tuv' as'String'
from [fn_split_1]('abc,def,efg,hij,lkm,nop,qrs,tuv',',') where Id<7 and id>2输入输出结果
Inputs String is : 'abc,def,efg,hij,lkm,nop,qrs,tuv'
String between 2nd and 7th comma : 'efg,hij,lkm,nop,qrs'
LengthOfChar(b/w 2 and 7 comma) : 19
The result from the function would be
ID Values
1 abc
2 def
3 efg
4 hij
5 lkm
6 nop
7 qrs
8 tuv但我们不能忽视课文之间的逗号。是否有更多的优化方法来实现这一点？
回答 3
Stack Overflow用户
回答已采纳
发布于 2017-06-01 19:05:22
贝娄有一个基于XML &XQuery的解决方案：
DECLARE @Source NVARCHAR(100) = N'abc,def,efg,hij,lkm,nop,qrs,tuv'
DECLARE @Start INT = 2
DECLARE @End INT = 7
-- Solution #1
SELECT
(CONVERT(XML, N'<root><i>' + REPLACE(@Source, N',', N'</i><i>') + N'</i></root>'))
.query(N'for $t in (root/i[position() gt sql:variable("@Start") and position() le sql:variable("@End")]/text())
return <len>{string-length($t)}</len>')
.value('sum(len)', 'INT') + (@End - @Start - 1)演示
编辑1:将...query('...').query('sum(len)').value('.', 'INT')替换为...query('...').value('sum(len)', 'INT')
注意:假设源字符串不包含XML保留字符(例如。<)。如果这是你的案子告诉我。
Stack Overflow用户
发布于 2017-06-01 17:22:34
首先，我很痛苦地看到拆分/解析函数中的循环。
尽管如此，此选项还将返回序列号。
示例
声明@YourTable表(String varchar(max))插入@YourTable值('abc，def，efg，hij，lkm，nop，qrs，tuv')
Select A.*
,B.*
From @YourTable A
Cross Apply (
Select Value = sum(len(RetVal)+1)-1
From [dbo].[udf-Str-Parse-8K](A.String,',')
Where RetSeq between 2 and (7-1)
) B返回
String Value
abc,def,efg,hij,lkm,nop,qrs,tuv 19 (如果有兴趣的话) UDF
CREATE FUNCTION [dbo].[udf-Str-Parse-8K] (@String varchar(max),@Delimiter varchar(25))
Returns Table
As
Return (
with cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
cte2(N) As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a,cte1 b,cte1 c,cte1 d) A ),
cte3(N) As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter),
cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S)
Select RetSeq = Row_Number() over (Order By A.N)
,RetVal = LTrim(RTrim(Substring(@String, A.N, A.L)))
From cte4 A
);
--Orginal Source http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||')只是为了好玩，
Select * From [dbo].[udf-Str-Parse-8K]('abc,def,efg,hij,lkm,nop,qrs,tuv',',')返回
RetSeq RetVal
1 abc
2 def
3 efg
4 hij
5 lkm
6 nop
7 qrs
8 tuvStack Overflow用户
发布于 2017-06-01 17:43:57
你要分开吗？你可以找到每个‘N’字符的位置，并得到区别。
CREATE FUNCTION dbo.NthCharIndex
(
@CharToFind varchar(8000),
@StringToSearch varchar(8000),
@N int
)
RETURNS int
AS
BEGIN
DECLARE @pos int, @ctr int, @ret int
SET @pos = 0
SET @ctr = 0
BEGIN
WHILE(@ctr < @N)
BEGIN
SELECT @ret = CHARINDEX(@CharToFind, @StringToSearch, @pos + 1)
SET @ctr = @ctr + 1
SET @pos = @ret
END
END
RETURN(@ret)
END
GO那你只需要找出不同之处。
Select dbo.nthCharIndex('a', 'bananaBanana', 6) - dbo.nthCharIndex('a', 'bananaBanana', 1)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/44313187
复制相关文章
相似问题
腾讯云开发者
