Friday, January 23, 2015

SQL Puzzle

 My friend came to me and can you find this word 'S L U N O I O T'. I said 'SOLUTION'.
Then I thought why can't I create a script which will jigjag a input word.

Of course you may do so many other ways.. I have developed like below. If you want to use this for longer strings please change the string length appropriately...


SET NOCOUNT ON
GO
Declare @str VARCHAR(20) = 'SOLUTION' -- You can pass your own word here
DECLARE @val CHAR(1)

DECLARE @valstr VARCHAR(20)
DECLARE @len INT = LEN(@str)
Declare @mytab Table (ID INT IDENTITY,Value char(1),OrderID INT)
DECLARE @i int =1
    WHILE (@len >= @i)
    BEGIN
        SET @val = SUBSTRING(@str,@i,1)
        INSERT INTO @mytab VALUES (@val,rand()*10)
        SET @valstr = SUBSTRING(@str,@i,@len)
        --SET @len = Len(@valstr)
        SET @i = @i +1
    END

SELECT *, row_number() OVER (ORDER BY orderID) RowID into #temp from @mytab


DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + value
FROM #temp
 SELECT replace(@names,',','') As WhatIsThisWord
 drop table #temp

Note: Please acknowledge.

No comments:

Post a Comment