May 12, 2013 at 2:39 am
Dear,
I have a String='A,B,C'.
I want to find A, B, C individually using substing function. There may be 3 select statements. But I can't find the 2nd Comma(',') from that string.
Please help me.
May 12, 2013 at 8:22 am
You can use below script to split the string with comma
DECLARE @s-2 varchar(max),
@Split char(1),
@X xml
SELECT @s-2 = 'A,B,C',
@Split = ','
SELECT @X = CONVERT(xml,'<root><splittedvalues>' + REPLACE(@S,@Split,'</splittedvalues><splittedvalues>') + '</splittedvalues></root>')
SELECT Temp.splittedvalues.value('.','varchar(20)')
FROM @X.nodes('/root/splittedvalues') Temp(splittedvalues)
May 12, 2013 at 9:44 am
shohelr2003 (5/12/2013)
Dear,I have a String='A,B,C'.
I want to find A, B, C individually using substing function. There may be 3 select statements. But I can't find the 2nd Comma(',') from that string.
Please help me.
Try this funcion:
CREATE FUNCTION [dbo].[itvfFindPosTally]
(
@strInput VARCHAR(8000)
,@delimiter VARCHAR(5)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b
),
E4(N) AS (SELECT 1 FROM E2 a, E2 b
),
cteTally(N) AS (SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@strInput,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
findchar (posnum,pos)
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY t.N) AS posnum
,CHARINDEX(@delimiter,@strInput,t.N) AS pos
FROM
cteTally t
WHERE
(SUBSTRING(@strInput,t.N,1) = @delimiter)
)
SELECT
posnum
,pos
FROM
findchar
WHERE
pos > 0
)
May 12, 2013 at 11:54 am
Hello dear,
please tryout this . I assume from your query string is not limited to 2 commas .
Declare @strsql varchar(50),@chrindex int
set @strsql = 'AA,BB,C,D'
select @strsql --print original val
select @chrindex=CHARINDEX(',',@strsql,1)
select SUBSTRING(@strsql,1,@chrindex-1)--output1
while @chrindex > 0
begin
set @strsql= SUBSTRING(@strsql,@chrindex+1,LEN(@strsql))
select @chrindex=CHARINDEX(',',@strsql,1)
if @chrindex >0 select SUBSTRING(@strsql,1,@chrindex-1) -- output2
end
select @strsql --output3
May 12, 2013 at 12:22 pm
atheeth (5/12/2013)
Hello dear,please tryout this . I assume from your query string is not limited to 2 commas .
Declare @strsql varchar(50),@chrindex int
set @strsql = 'AA,BB,C,D'
select @strsql --print original val
select @chrindex=CHARINDEX(',',@strsql,1)
select SUBSTRING(@strsql,1,@chrindex-1)--output1
while @chrindex > 0
begin
set @strsql= SUBSTRING(@strsql,@chrindex+1,LEN(@strsql))
select @chrindex=CHARINDEX(',',@strsql,1)
if @chrindex >0 select SUBSTRING(@strsql,1,@chrindex-1) -- output2
end
select @strsql --output3
I think you will find the function posted above by Steven Willis to be faster and more scaleable than what you have posted. Try them both out against a million row table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply