Splitting string with deliminator In SQL Server

During my work I came to a problem where I had to extract some numbers from a comma

separated list of numbers in TSQL 2008 (Sql Server).

I had to extract last seven numbers from a comma separated list of 14 numbers.

Just for the start I created a table function called ufn_Split.

This function takes two arguments:

1. Data

2. Deliminator

And it returns the split data in a tabular format.

Below is the function :

CREATE FUNCTION [dbo].[ufn_Split]<br /> (<br /> @Data NVARCHAR(2000),<br /> @SplitOn NVARCHAR(5)<br /> ) <br /> RETURNS @RtnValue TABLE <br /> (<br /> rID INT IDENTITY(1,1),<br /> DATA NVARCHAR(100)<br /> ) <br /> AS <br /> BEGIN <br /> WHILE (CHARINDEX(@SplitOn,@Data)>0)<br /> BEGIN<br /> INSERT <br /> @RtnValue (DATA)<br /> SELECT <br /> LTRIM(RTRIM(SUBSTRING(@Data, 1, CHARINDEX(@SplitOn, @Data) -1)))<br /> <br /> SET @Data = SUBSTRING(@Data,CHARINDEX(@SplitOn, @Data) + 1, LEN(@Data))<br /> END<br /> <br /> INSERT <br /> @RtnValue (DATA)<br /> SELECT <br /> LTRIM(RTRIM(@Data))<br /> <br /> RETURN<br /> END <br />

___________________________________________________________________

Mitul Patel

http://dot-net-solutions.blogspot.com/

http://www.omkarsolutions.com.au/

___________________________________________________________________

I recommend the following books to improve your T-SQL Skills.