SQL SERVER – Reverse String Word By Word – Part 2

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.

 SQL SERVER - Reverse String Word By Word - Part 2 reverse-string-test

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)

SQL Function, SQL Scripts, SQL String
Previous Post
SQL SERVER – Can Database Primary File Have Any Other Extention Than MDF
Next Post
SQL SERVER – Proving that the Source of the Problems aren’t Tied to the Database

Related Posts

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.

    Reply
  • Hi

    nice article, but the SQL needs to be formatted with indentation, white space etc… let’s make it easy to read :-)

    Thanks
    Ian

    Reply

Leave a Reply