fuction to split by comma from a string

Nikki

8y1.2kSQL

Hi,

I have a string in sql like '200,209.4,450,680.02,3500,3400.02,3000,123,855.98'

I want a rsult by function like this

1     200
2    209.4
3    450
4    680.02
5    3500
6    3400.02
7    3000
8    123
9    855.98

Please help me.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Code Alone8y ago
Accepted answer
Hi,
I have a below function, which fulfill your requirements
CREATE FUNCTION [dbo].[SplitPra] (@Value VARCHAR(MAX), @delimiter CHAR)
RETURNS @DataResult TABLE([ID] TINYINT IDENTITY(1,1),[Value] NVARCHAR(128))
AS
BEGIN
DECLARE @XML xml = N' + REPLACE(@Value, @delimiter, ']]>) + ']]>'
INSERT INTO @DataResult ([Value])
SELECT RTRIM(LTRIM(T.c.value('.', 'NVARCHAR(128)')))
FROM @xml.nodes('//r') T(c)
RETURN
END
To call this function use below lines
SELECT * FROM SplitPra('200,209.4,450,680.02,3500,3400.02,3000,123,855.98',',')
+2
Hardik Deshani8y ago
All answers are correct but this one is new. If you are using SQL Version above 2012 then below query work

SELECT * FROM string_split('200,209.4,450,680.02,3500,3400.02,3000,123,855.98',',')

in above 2012 SQL version string_split is inbuilt function
+1
Code Alone8y ago
Find complete descriptive article on this issue on :

https://www.c-sharpcorner.com/article/tabular-function-to-split-a-delimiter-separated-string-to-rows-with-their-positi/
+1
Nikki8y ago
Thank you Suraj and Pradeep for your quick reverts.
These solutions are working.
0
Suraj Kumar8y ago
Hello Nikita,

You can write a function and pass parameter of comma separated value in function and it will give the result as needed.

You need to call function as below

SELECT * FROM dbo.BreakStringIntoRows(''200,209.4,450,680.02,3500,3400.02,3000,123,855.98'')

--Using the UDF to convert comma separated values into rows

--SELECT * FROM dbo.BreakStringIntoRows('Apple,Banana,Orange')

CREATE FUNCTION dbo.BreakStringIntoRows (@CommadelimitedString varchar(1000))

RETURNS @Result TABLE (Column1 VARCHAR(100))

AS

BEGIN

DECLARE @IntLocation INT

WHILE (CHARINDEX(',', @CommadelimitedString, 0) > 0)

BEGIN

SET @IntLocation = CHARINDEX(',', @CommadelimitedString, 0)

INSERT INTO @Result (Column1)

--LTRIM and RTRIM to ensure blank spaces are removed

SELECT RTRIM(LTRIM(SUBSTRING(@CommadelimitedString, 0, @IntLocation)))

SET @CommadelimitedString = STUFF(@CommadelimitedString, 1, @IntLocation, '')

END

INSERT INTO @Result (Column1)

SELECT RTRIM(LTRIM(@CommadelimitedString))--LTRIM and RTRIM to ensure blank spaces are removed

RETURN

END

Thanks
+2