We have some rows that we need to do some tricky string manipulation
on.

We have a UserID column which has userid entries in the format
firstname.lastname and i need to change each entry to
lastname.firstname

Can this be done by some script?

Thanks so much for your help.

Sid

Try the following :

CREATE TABLE
UserNamesList(UserName varChar(50) NULL)

INSERT INTO UserNamesList SELECT 'Ryan.Offord' AS UserName
INSERT INTO UserNamesList SELECT 'Joe.Somebody' AS UserName

SELECT
UserName,
RIGHT(UserName, CharIndex('.',Reverse(UserName))-1) + '.' +
LEFT(UserName, CharIndex('.',(UserName))-1) AS Swapped

FROM
UserNamesList

DROP TABLE UserNamesList

It should swap them over using the full stop as the point where it needs
to do this.

Hope that helps

Ryan


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Ryan <anonymous@devdex.com> wrote in message news:<417fb2aa$0$24932$c397aba@news.newsgroups.ws> ...
> Try the following :
>
> CREATE TABLE
> UserNamesList(UserName varChar(50) NULL)
>
> INSERT INTO UserNamesList SELECT 'Ryan.Offord' AS UserName
> INSERT INTO UserNamesList SELECT 'Joe.Somebody' AS UserName
>


Do i have to create a new table inserting each record manually from
the existing table? is there any other way to do this

siddhartha mulpuru (justkule@yahoo.com) writes:
> Do i have to create a new table inserting each record manually from
> the existing table? is there any other way to do this

No, you could use an UPDATE statement, working from Ryan's sample.

Generally, when you post a question asking for a query, it is good
advice to post:

o CREATE TABLE statement for your table
o INSERT statements with sample data.
o The desired result.

This makes it easy for anyone to answers to cut and paste into Query
Analyzer to give you a tested solution. You did not post any of this,
and Ryan still took the task to post a tested solution, but he did it
in his way, so you could see the technique.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp