Earlier I wrote a blog post about SQL SERVER – Reverse String Word By Word. SQL Server Expert Sanjay Monpara wrote a great comment to the blog post where he has proposed alternate solution. Right after the solution of the Sanjay, another expert Yuri Petrov suggested a modification to the script. I have taken in consideration both the comments and solution and build a more robust script over here.
Here is the script to create a function
CREATE FUNCTION [dbo].[Split] (@string AS VARCHAR(8000), @splitAt AS VARCHAR(8000)) RETURNS @strings TABLE (ID INT, Strings VARCHAR(8000)) AS BEGIN DECLARE @splitLen AS INT DECLARE @index AS INT DECLARE @id AS INT SET @id = 0 SET @splitLen = LEN(@splitAt) SET @splitAt = '%' + @splitAt + '%' SET @index = PATINDEX(@splitAt, @string) WHILE(@index > 0) BEGIN SET @id = @id+1 INSERT INTO @strings VALUES(@id,SUBSTRING(@string, 1, @index-1)) SET @string = SUBSTRING(@string, @index + 1, LEN(@string)) SET @index = PATINDEX(@splitAt, @string) END IF LEN(@string) > 0 BEGIN SET @id = @id+1 INSERT INTO @strings VALUES(@id, @string) END RETURN END
Here is how you will use the function:
DECLARE @Input NVARCHAR(MAX) SET @Input = 'This is Reverse String test' DECLARE @Output NVARCHAR(MAX) SET @Output = '' SELECT @Output = strings + ' ' + @Output FROM dbo.split(@Input,' ') ORDER BY ID PRINT @Output
The above script will return results just as we have seen earlier blog post.
Thanks Sanjay for awesome contribution.
In addition to the above Yuri Petrov has also suggested another script which we will see in future blog posts.
Reference: Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
Thank you Pinal & Yuri :)
I am regularly using split function for different scenarios
Mostly I am using this function in multi(comma seperated) value parameter to avoid dynamic sql.
Another advantage is that we can use this function for mostly all datatypes columns and no need to worry about separator.
e.g.
declare @csvList nvarchar(max)
SET @csvList = ‘1,3,5,6’
SELECT * FROM employees where employeeid in (SELECT strings from dbo.split(@csvList,’,’))
SET @csvList = ‘1#3#5#6′
SELECT * FROM employees where employeeid in (SELECT strings from dbo.split(@csvList,’#’))
SET @csvList = ‘Nancy;Janet;Michael’
SELECT * FROM employees where firstname in (SELECT strings from dbo.split(@csvList,’;’))
SET @csvList = ‘1952-02-19,1963-08-30,1955-03-04,1960-05-29′
SELECT * FROM employees where birthdate in (SELECT strings from dbo.split(@csvList,’,’))
In which another situations we can use this function?
Suggestions welcome from all.
Thanks.
Hi
nice article, but the SQL needs to be formatted with indentation, white space etc… let’s make it easy to read :-)
Thanks
Ian