Split by comma in SQL - 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)

Split by comma in SQL

Author

Topic

shinelawrence
Starting Member

32 Posts

Posted - 2013-09-30 : 05:49:13

Hi Everyone,
I need one help, i got one variable value in stored procedure, the value is '999,989,889' now i want to split by comma... it is dynamically value sometimes have 3 values sometime like this '999,989,889,778'. i want to split it.. Pls tell the solution... How to do....

Thanks & Regards

Lawce

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-30 : 06:34:48

see
http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

shinelawrence
Starting Member

32 Posts

Posted - 2013-09-30 : 07:19:46

thank u very much....

Lawce

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-30 : 10:01:02

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kameswararao polireddy
Starting Member

19 Posts

Posted - 2013-10-03 : 04:56:18

Hi..,
You may use this one too

create FUNCTION Split (
@InputString VARCHAR(8000),
@Delimiter VARCHAR(50)
)

RETURNS @Items TABLE (
Item VARCHAR(8000)
)

AS
BEGIN
IF @Delimiter = ' '
BEGIN
SET @Delimiter = ','
SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
END

IF (@Delimiter IS NULL OR @Delimiter = '')
SET @Delimiter = ','

--INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic
--INSERT INTO @Items VALUES (@InputString) -- Diagnostic

DECLARE @Item VARCHAR(8000)
DECLARE @ItemList VARCHAR(8000)
DECLARE @DelimIndex INT

SET @ItemList = @InputString
SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
WHILE (@DelimIndex != 0)
BEGIN
SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
INSERT INTO @Items VALUES (@Item)

-- Set @ItemList = @ItemList minus one less item
SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
END -- End WHILE

IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
BEGIN
SET @Item = @ItemList
INSERT INTO @Items VALUES (@Item)
END

-- No delimiters were encountered in @InputString, so just return @InputString
ELSE INSERT INTO @Items VALUES (@InputString)

RETURN

END -- End Function

P.Kameswara rao

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources