在给定的字符串集中查找字符串以逗号分隔 [英] finding string in given set of strings bt comma separated
问题描述
我需要从下面的字符串中获取第三个子字符串是样本,使用sql server 2008.
string1: - ('''C20080703115333MCTTtettett'',' '24'',''6101349328'',''捆绑标准'',''4'',''2.00'',''测试插入新SP'','',''PD2013021002584832540'')< br $>
预期结果: - 6101349328
string2: - ('''C20080703115333MCTTetew'',''24' ',''7101349328'',''捆绑标准'',''4'',''2.00'',''测试插入新SP'','',''PD2013021002584832540'')
期望的结果: - 7101349328
string3: - ('''C20080703115333MCTTteetew'',''24tt'',' '8101349328'',''捆绑标准'',''4'',''2.00'',''测试插入新SP'','',''PD2013021002584832540'')
期望的结果: - 8101349328
string4: - ('''C20080703'',''24'',''111101349328'',''捆绑标准'', ''4'',''2.00'',''测试插入新SP'','',''PD2013021002584832540'')
所需结果: - 111101349328
在此先感谢。
i need to get third sub string from a string below are the samples, using sql server 2008.
string1:- (''C20080703115333MCTTtettett'',''24'',''6101349328'',''Bundled Standard'',''4'',''2.00'',''Testing Insert for New SP'','',''PD2013021002584832540'')
desired result:- 6101349328
string2:- (''C20080703115333MCTTetew'',''24'',''7101349328'',''Bundled Standard'',''4'',''2.00'',''Testing Insert for New SP'','',''PD2013021002584832540'')
desired result:- 7101349328
string3:- (''C20080703115333MCTTteetew'',''24tt'',''8101349328'',''Bundled Standard'',''4'',''2.00'',''Testing Insert for New SP'','',''PD2013021002584832540'')
desired result:- 8101349328
string4:- (''C20080703'',''24'',''111101349328'',''Bundled Standard'',''4'',''2.00'',''Testing Insert for New SP'','',''PD2013021002584832540'')
desired result:- 111101349328
Thanks In advance.
推荐答案
在您的数据库中创建此功能
create this function in your database
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ParseValues]
(@String varchar(8000), @Delimiter varchar(max) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(max))
AS
BEGIN
DECLARE @Value varchar(max)
WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(
@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END,
@String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(
@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END
现在,执行以下查询你w得到了预期的结果
now,execute below query you will get desired result
with a as
(
select '''C20080703115333MCTTtettett'',''24'',''6101349328'',''Bundled Standard'',''4'',''2.00'',''Testing Insert for New SP'','',''PD2013021002584832540''' as c1
union all
select '''C20080703115333MCTTetew'',''24'',''7101349328'',''Bundled Standard'',''4'',''2.00'',''Testing Insert for New SP'','',''PD2013021002584832540'''
union all
select '''C20080703115333MCTTteetew'',''24tt'',''8101349328'',''Bundled Standard'',''4'',''2.00'',''Testing Insert for New SP'','',''PD2013021002584832540'''
union all
select '''C20080703'',''24'',''111101349328'',''Bundled Standard'',''4'',''2.00'',''Testing Insert for New SP'','',''PD2013021002584832540'''
)
select c1,val
from a
cross apply dbo.parsevalues(c1,''',''') as b where b.ID = 3 ;
快乐编码!
:)
Happy Coding!
:)
select
SUBSTRING('abc,xyz,pqr321,123',CHARINDEX(',','abc,xyz,pqr321,123',CHARINDEX(',','abc,xyz,pqr321,123')+1)+1,CHARINDEX(',','abc,xyz,pqr321,123',CHARINDEX(',','abc,xyz,pqr321,123',CHARINDEX(',','abc,xyz,pqr321,123')+1)+1)-(CHARINDEX(',','abc,xyz,pqr321,123',CHARINDEX(',','abc,xyz,pqr321,123')+1)+1))
将此示例中的示例字符串替换为您的
Replace the sample string in this example with your one
这篇关于在给定的字符串集中查找字符串以逗号分隔的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!