String manipulation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

String manipulation

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-22 : 09:06:16

Scott writes "I have a music database that has artists listed as "Dylan, Bob". Is there a way to page through the records and flip the names around and insert them into another field called artist2 in the format "Bob Dylan"?"

Nazim
A custom title

1408 Posts

Posted - 2002-04-22 : 10:10:58

update tablename set name= rtrim(substring(name,1,charindex(',',name)-1))+' '+
+ltrim(substring(name,charindex(',',name)+2,len(name)))

--------------------------------------------------------------

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-04-22 : 10:19:03

mmmmm fun with string functions. There are lots of goodies in books online.
But this will give you some ideas

Declare @artist varchar(100)

Select @Artist = 'Dylan, Bob'

Select @artist,

Substring(@Artist, charindex(',', @Artist)+1, len(@Artist))
+ ' ' +
Substring(@Artist, 1, charindex(',', @Artist)-1)

However, it would be much better database design to store the names in separate columns.

I always felt that Hendrix did Dylan much better than Dylan ever does