fuction to split by comma from a string

Question

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.

Code Alone · 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' , ',' )

Code Alone · Answer

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/

Suraj Kumar · Answer

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

Hardik Deshani · Answer

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

Nikki · Answer

Thank you Suraj and Pradeep for your quick reverts. These solutions are working.