Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find the second comma Expand / Collapse
Author
Message
Posted Sunday, May 12, 2013 2:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 07, 2014 4:55 AM
Points: 175, Visits: 470
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.
Post #1451886
Posted Sunday, May 12, 2013 8:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 15, 2013 10:01 AM
Points: 1, Visits: 4
You can use below script to split the string with comma

DECLARE @S varchar(max),
@Split char(1),
@X xml

SELECT @S = '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)
Post #1451905
Posted Sunday, May 12, 2013 9:44 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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
)


Post #1451909
Posted Sunday, May 12, 2013 11:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 7:40 AM
Points: 47, Visits: 151
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

Post #1451918
Posted Sunday, May 12, 2013 12:22 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 22,492, Visits: 30,199
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1451921
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse