Thursday, May 15, 2008

SQL string split function

This can't be the best SQL string split function around, and it's the 3rd string split function I've borrowed to solve a problem. To stop spaghetti creep, I've got to choose one and go with it.I could pick one of the 3 string split functions I'm using, but maybe if I poll the community at large with a large pole I'll find some string split function out there that's better than sliced bread.
CREATE FUNCTION dbo.Split
( @RowData nvarchar(2000), @SplitOn nvarchar(5))
RETURNS @RtnValue table
( Id int identity(1,1), Data nvarchar(100))
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1
While (Charindex(@SplitOn,@RowData)>0)

Begin
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

Set @Cnt = @Cnt + 1
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return

END

No comments:

Split the String values with a special character in MS Flow to convert this into Array

 Many times we have a requirement to prepare the Mailing address for some of the documents, suppose there are Address Line1, Address Line2, ...