Need to Swap the word in SQL

Question

Post reply

Need to Swap the word in SQL

Saravanan_tvr

SSCarpal Tunnel

Points: 4558
More actions
July 1, 2010 at 3:36 am

#237168

Hi all,
I have a column with the name of Author more than 1 lakh, examples value
Friel, Joe
But i need to show this value in front end like Joe Friel
can any tell me how to manipulate that in TSQL Common separator is ,(Comma)

Viewing 7 posts - 1 through 6 (of 6 total)

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Try this sir:

declare @stringtable table

( string varchar(20) )

insert into @stringtable select 'Friel, Joe'

select RIGHT(string,( len(string ) - charindex(',',string)) )+ ' ' + left (string,( charindex(',',string) -1 ))

from @stringtable

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 2

Thank u sir,

I forgot the charindex functions.. thanks for you output...

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Saravanan_tvr (7/1/2010)
Thank u sir,
I forgot the charindex functions.. thanks for you output...

No issues, welcome saravanan!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Just bear in mind that the above query will break if there are any rows in the table that don't have a , somewhere in that string. If there's a possibility of that happening, add a where clause that limits to rows that do have a comma in the string.

declare @stringtable table

( string varchar(20) )

insert into @stringtable values ('Friel, Joe')

insert into @stringtable values ('Someone Else')

select RIGHT(string,( len(string ) - charindex(',',string)) )+ ' ' + left (string,( charindex(',',string) -1 ))

from @stringtable

Msg 536, Level 16, State 5, Line 6

Invalid length parameter passed to the SUBSTRING function.

select RIGHT(string,( len(string ) - charindex(',',string)) )+ ' ' + left (string,( charindex(',',string) -1 ))

from @stringtable

where charindex(',',string) > 0

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Saravanan_tvr SSCarpal Tunnel Points: 4558 More actions · Answer 5

Small changes i done from your query

SELECT TOP 100 Author,LEFT(author,CHARINDEX(',',author)-1)AS FIRST_PART,

SUBSTRING(author, CHARINDEX(',',author)+1,LEN(author) ) AS SECOND_PART

FROM Table WHERE Author LIKE '%,%'

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

Saravanan_tvr (7/1/2010)
Small changes i done from your query
SELECT TOP 100 Author,LEFT(author,CHARINDEX(',',author)-1)AS FIRST_PART,
SUBSTRING(author, CHARINDEX(',',author)+1,LEN(author) ) AS SECOND_PART
FROM CTBR WHERE Author LIKE '%,%'

Yes, I was just in the process of writing you needed to add

( Charindex(',', string) ) - 1 ))

To get rid of the "space" that was prefixing the cell.

RIGHT(string, ( Len(string) - ( Charindex(',', string) ) - 1 )) + ' ' + LEFT(string, ( Charindex(',', string) - 1 ))

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/