declare @str varchar(100)='My name is Shiva' --Need output like : Shiva is name My

Question

Post reply

declare @str varchar(100)='My name is Shiva' --Need output like : Shiva is name My

Shiva N

Hall of Fame

Points: 3125
More actions
December 2, 2014 at 5:18 am

#312825

declare @STR varchar(100)='My name is Shiva'
--Need output like : Shiva is name My
Thanks,
Shiva N
Database Consultant

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Use the excellent splitter function by Jeff Moden[/url] and use a space as the delimiter.

Sort the results in descending order and concatenate them back together[/url].

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

Just and only for fun, here is a slightly twisted solution, recommend following Koen's suggestion

😎

DECLARE @STR VARCHAR(100)='My name is Shiva';

DECLARE @XSTR VARCHAR(MAX) = 'SELECT(SELECT CHAR(32)+Y.XN FROM(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS XI,XN FROM (VALUES'+

CHAR(40)+CHAR(39)+REPLACE(@STR,CHAR(32),CHAR(39)+CHAR(41)+CHAR(44)+CHAR(40)+CHAR(39))+CHAR(39)+CHAR(41)+') AS X(XN)) AS Y ORDER BY Y.XI DESC

FOR XML PATH(''''),TYPE).value(''.[1]'',''varchar(100)'')';

EXEC (@XSTR);

Result

Shiva is name My

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Eirikur Eiriksson (12/2/2014)
Just and only for fun, here is a slightly twisted solution, recommend following Koen's suggestion
😎
DECLARE @STR VARCHAR(100)='My name is Shiva';
DECLARE @XSTR VARCHAR(MAX) = 'SELECT(SELECT CHAR(32)+Y.XN FROM(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS XI,XN FROM (VALUES'+
CHAR(40)+CHAR(39)+REPLACE(@STR,CHAR(32),CHAR(39)+CHAR(41)+CHAR(44)+CHAR(40)+CHAR(39))+CHAR(39)+CHAR(41)+') AS X(XN)) AS Y ORDER BY Y.XI DESC
FOR XML PATH(''''),TYPE).value(''.[1]'',''varchar(100)'')';
EXEC (@XSTR);
Result
Shiva is name My

If you want people to be able to understand your code, follow my suggestion 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 4

Koen Verbeeck (12/2/2014)
Eirikur Eiriksson (12/2/2014)
Just and only for fun, here is a slightly twisted solution, recommend following Koen's suggestion
😎
DECLARE @STR VARCHAR(100)='My name is Shiva';
DECLARE @XSTR VARCHAR(MAX) = 'SELECT(SELECT CHAR(32)+Y.XN FROM(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS XI,XN FROM (VALUES'+
CHAR(40)+CHAR(39)+REPLACE(@STR,CHAR(32),CHAR(39)+CHAR(41)+CHAR(44)+CHAR(40)+CHAR(39))+CHAR(39)+CHAR(41)+') AS X(XN)) AS Y ORDER BY Y.XI DESC
FOR XML PATH(''''),TYPE).value(''.[1]'',''varchar(100)'')';
EXEC (@XSTR);
Result
Shiva is name My
If you want people to be able to understand your code, follow my suggestion 😀

time next the mind in that keep will I:hehe:

😎

Shiva N Hall of Fame Points: 3125 More actions · Answer 5

Hi Mr. Eirikur Eiriksson

Its working fine and exactly it is what i need.

so thank you very much...

Thanks,
Shiva N
Database Consultant

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 6

Shiva N (12/2/2014)
Hi Mr. Eirikur Eiriksson
Its working fine and exactly it is what i need.
so thank you very much...

Do you also understand the code? If it crashes on your production server, people will ask you questions and you are responsible.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Umer Akram SSC-Insane Points: 22209 More actions · Answer 7

Koen Verbeeck (12/2/2014)
Eirikur Eiriksson (12/2/2014)
Just and only for fun, here is a slightly twisted solution, recommend following Koen's suggestion
😎
DECLARE @STR VARCHAR(100)='My name is Shiva';
DECLARE @XSTR VARCHAR(MAX) = 'SELECT(SELECT CHAR(32)+Y.XN FROM(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS XI,XN FROM (VALUES'+
CHAR(40)+CHAR(39)+REPLACE(@STR,CHAR(32),CHAR(39)+CHAR(41)+CHAR(44)+CHAR(40)+CHAR(39))+CHAR(39)+CHAR(41)+') AS X(XN)) AS Y ORDER BY Y.XI DESC
FOR XML PATH(''''),TYPE).value(''.[1]'',''varchar(100)'')';
EXEC (@XSTR);
Result
Shiva is name My
If you want people to be able to understand your code, follow my suggestion 😀

+1 😀

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 8

twin.devil (12/3/2014)
Koen Verbeeck (12/2/2014)
Eirikur Eiriksson (12/2/2014)
Just and only for fun, here is a slightly twisted solution, recommend following Koen's suggestion
😎
DECLARE @STR VARCHAR(100)='My name is Shiva';
DECLARE @XSTR VARCHAR(MAX) = 'SELECT(SELECT CHAR(32)+Y.XN FROM(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS XI,XN FROM (VALUES'+
CHAR(40)+CHAR(39)+REPLACE(@STR,CHAR(32),CHAR(39)+CHAR(41)+CHAR(44)+CHAR(40)+CHAR(39))+CHAR(39)+CHAR(41)+') AS X(XN)) AS Y ORDER BY Y.XI DESC
FOR XML PATH(''''),TYPE).value(''.[1]'',''varchar(100)'')';
EXEC (@XSTR);
Result
Shiva is name My
If you want people to be able to understand your code, follow my suggestion 😀
+1 😀

Ooops, my bad:Whistling:, that's almost readable, here's a !fix

😎

DECLARE @STR VARCHAR(100)='My name is Shiva';

DECLARE @XSTR VARCHAR(MAX) = REPLICATE(0x53454C4543542853454C4543542043484152283332292B592E584E2046524F4D2853454C45435420524F575F4E554D4245522829204F56455220284F52444552204259202853454C454354204E554C4C29292041532058492C584E2046524F4D202856414C5545532827,1)+

REPLACE(@STR,0x20,0x27292C2827)+LTRIM(0x272929204153205828584E29292041532059204F5244455220425920592E5849204445534320464F5220584D4C2050415448282727292C54595045292E76616C756528272E5B315D272C277661726368617228313030292729);

EXEC (@XSTR);