How to Split Microsoft SQL Server Table Row Data

Introduction

This tip shows you an easy way to split Microsoft SQL Server table row data.

Using the Code

A sample code snippet is given below:

-- SELECT @tableColumnID, Data  FROM dbo.[FUN_split_row_data](@p_row_string_data,',')
CREATE FUNCTION [dbo].[FUN_split_row_data]
(
	@p_row_data NVARCHAR(2000),
	@p_split_on NVARCHAR(256)
)  
RETURNS @g_ret_value TABLE 
(
	id INT idENTITY(1,1),
	data nvarchar(100)
) 
AS  
BEGIN 
	DECLARE @g_counter INT
	SET @g_counter = 1

	WHILE (CHARINDEX(@p_split_on,@p_row_data)>0)
	BEGIN
		INSERT INTO @g_ret_value (data)
		SELECT data = LTRIM(RTRIM(SUBSTRING(@p_row_data,1,CHARINDEX(@p_split_on,@p_row_data)-1)))

		SET @p_row_data = SUBSTRING(@p_row_data,CHARINDEX(@p_split_on,@p_row_data)+1,LEN(@p_row_data))
		SET @g_counter = @g_counter + 1
	END
	
	INSERT INTO @g_ret_value (data)
	SELECT data = LTRIM(RTRIM(@p_row_data))

	RETURN

END

Input

SELECT  1234 AS id, data  FROM dbo.[FUN_split_row_data]_
('Hello,world,!!!,you are, most, welcome, at, codeproject.com',',') 

Output

id    data

------------------
314    Hello
314    world
314    !!!
314    you are
314    most
314    welcome
314    at
314    codeproject.com

Conclusion

I hope you guys get the scenario and this might be helpful to you. Enjoy!

History

• Saturday, December 19^th, 2015: Initial post