473,324 Members | 2,313 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

String Manipulation SQL

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
Jul 20 '05 #1
3 7571
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!
Jul 20 '05 #2
Ryan <an*******@devdex.com> wrote in message news:<41**********************@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
Jul 20 '05 #3
siddhartha mulpuru (ju******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Dim | last post by:
I found that C# has some buggy ways to process string across methods. I have a class with on global string var and a method where i add / remove from this string Consider it a buffer... with some...
32
by: tshad | last post by:
Can you do a search for more that one string in another string? Something like: someString.IndexOf("something1","something2","something3",0) or would you have to do something like: if...
29
by: zoro | last post by:
Hi, I am new to C#, coming from Delphi. In Delphi, I am using a 3rd party string handling library that includes some very useful string functions, in particular I'm interested in BEFORE (return...
4
by: WaterWalk | last post by:
Hello, I'm currently learning string manipulation. I'm curious about what is the favored way for string manipulation in C, expecially when strings contain non-ASCII characters. For example, if...
10
by: micklee74 | last post by:
hi if i have a some lines like this a ) "here is first string" b ) "here is string2" c ) "here is string3" When i specify i only want to print the lines that contains "string" ie the first...
5
by: Niyazi | last post by:
Hi, Does anyone knows any good code for string manipulation similar to RegularExpresion? I might get a value as string in a different format. Example: 20/02/2006 or 20,02,2006 or ...
3
by: crprajan | last post by:
String Manipulation: Given a string like “This is a string”, I want to remove all single characters( alphabets and numerals) like (a, b, 1, 2, .. ) . So the output of the string will be “This is...
7
Frinavale
by: Frinavale | last post by:
I currently have a .NET application that has an object which passes a string (a connection string) as a parameter to another object that does database manipulation. This string isn't stored...
3
by: frankeljw | last post by:
I have 2 Java strings 1st String is a series of names, colons, and numbers ie) Name1:13:Name2:4526:Name3:789:Name4:3729:Name5:6:Name6:44 2nd String is a name ie) Name2 I need to get the...
22
by: mann_mathann | last post by:
can anyone tell me a solution: i cannot use the features in standard c++ string classgh i included the string.h file but still its not working.
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.