Query help. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query help.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2012-04-12 : 18:03:18

[code]I need to parse the whole string to desire output below. The sequel statement below is work but I wondered if there are different way
to do it. I need to insert into temp table below.

Thanks for your help.

---------------------------------------------------

DECLARE @SQLStr VARCHAR(5000)
SET @SQLStr = '4,4,6,6,11,11,23,23,27,27,28,28,29,29,30,30,33,33,37,37,40,40,43,43,45,45,46,46,51,51,58,58,63,63,66,66,68,68,69,69,71,71,76,76,81,81,82,82,83,83,85,85,86,86,87,87,88,88,89,89,90,90,91,91,96,96,99,99,100,100,104,104,105,105,107,107,112,112,118,118,120,120,122,122,127,127,129,129,131,132,132,133,133,136,136,137,137,138,138,149,149,157,157,158,158,160,160,161,161,166,166,167,167,172,172,173,173,174,174,176,176,179,179,181,181,182,182,190,190,191,191,193,193,200,200,202,202,208,208,209,209,210,210,212,216,216,220,220,221,221,223,223,224,224,230,230,231,231,235,235,238,238,239,239,241,241,242,242,244,244,249,249,250,250,251,251,253,253,254,254,257,257,262,262,268,268,272,272,274,274,279,279,280,280,282,282,283,283,284,284,287,287,298,298,302,302,303,303,306,306,309,309,310,313,313,315,316,316,317,317,318,318,320,320,322,322,323,323,325,325,327,327,328,328,329,329,330,330,332,333,333,340,340,341,341,342,342,343,343,345,345,347,347,350,350,352,354,354,362,367,367,372,372,373,373,376,378,379,379,385,403,423,600,998,999,999'

-- Desire results:

--PRINT @SQLStr
SQLStr
-----
100
104
105
107
11
112
118
120
122
...
999
----------------------------------------------------------------------------
-- Testing..
;WITH csvcte (i, j, s)
AS
(
SELECT i = 1,
s = CHARINDEX (',', @SQLStr + ','),
SUBSTRING (@SQLStr, 1, CHARINDEX(',', @SQLStr + ',') - 1 )
UNION ALL
SELECT i = (j + 1),
j = CHARINDEX(',', @SQLStr + ',', (j + 1) ),
SUBSTRING (@SQLStr, (j + 1), CHARINDEX(',', @SQLStr + ',', (j + 1)) - (j+ 1) )
FROM csvcte
WHERE (CHARINDEX ( ',', @SQLStr + ',', (j + 1) ) <> 0)
)
SELECT DISTINCT SUBSTRING (@SQLStr, i, (j - i)) AS 'Str'
FROM csvcte
ORDER BY 1 ASC
OPTION (MAXRECURSION 0);
GO

------------------------------------------------------------

DROP TABLE #temp
GO
CREATE TABLE #temp
(
store_nb INT NULL
)

INSERT #temp (store_nb)
SELECT @SQLStr
go[/code]

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-04-12 : 18:09:40

[code]Please go through this .

http://blog.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx[/code]

NguyenL71
Posting Yak Master

228 Posts

Posted - 2012-04-12 : 18:43:27

[code]I do not want to use function. I wondered if there is a way to do in Tsql.

Thank you.[/code]

quote:
Originally posted by vijays3

[code]Please go through this .

http://blog.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx[/code]

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-04-12 : 19:26:57

[code]
Declare @str varchar(50)

set @str = 'a,b,c'

set @str = @str + ','
--print @str
;with a as
(
select 1 as start , CHARINDEX(',',@str,1)AS [End]
UNION ALL
SELECT [End]+1 AS Start,CHARINDEX(',',@str,[End]+1)AS [End] from a where [End]<LEN(@str)
)

select SUBSTRING(@str,Start,[End]-Start)from a;
[/code]

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-13 : 03:02:02

This is the best way to split a split a CSV and this definitely is the best article I've read about it. Check out the link:

http://www.sqlservercentral.com/articles/T-SQL/62867/

Vinu Vijayan

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources