Solved

Delete Characters After a Space in a String

Posted on 2009-05-05
4
993 Views
Last Modified: 2012-05-06
I have had a quick search but can't seem to get a working method of this, and I can't imagine it is the most difficult thing to do.
I have a list of about 1000 names from a client, all in the 'surname, forename' format.
I am loading them into a database but want them to be in the 'Forename Surname' format.
I have managed to get some working code to get the forename only but now need something that will get the surname only, and a way to do that is get delete all characters after and including the comma.

Cheers in advance
0
Comment
Question by:Answering4uAnt
  • 2
4 Comments
 
LVL 4

Author Comment

by:Answering4uAnt
ID: 24303346
sorry topic title slightly wrong, I want to delete all characters after a comma but imagine the method will be pretty much the same

Cheers
0
 
LVL 6

Accepted Solution

by:
bokist earned 250 total points
ID: 24303392
Try this way :

update my_table set name_field = substring(name_field, charindex(',', name_field) +1, 50) + ' ' +
                                                       substring(name_field, 1, charindex(',', name_field) -1)  
0
 
LVL 4

Author Comment

by:Answering4uAnt
ID: 24303409
Thanks, that did the job just fine, it did leave an extra space at the start which I have managed to remove easily enough

cheers
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24303415
declare @name varchar(50)
set @name='Ritesh,Shah'
select replace(@name,substring(@name,charindex(',',@name),len(@name)),'')
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question