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)