May 19, 2010 at 3:27 pm
Ok, I have a database that was create way before my name. The person that created the db, put a field in the db which has the first and last name, both in the same field. I want to seperate those. So, all the names look like this "lastname, firstname" What I need to be able to do, because the last name could be any number of characters and the first name can be any number of characters, I will to run one query that will pull everything to the left of the comma, which should give me the last name. Then write a second query that will pull everything to the right of the comma, which should give me the first name. Any suggestions on how to do that?
Thanks in advanced.
Jordon
May 19, 2010 at 4:47 pm
declare @fullName varchar(50)
set @fullName = 'Smith, Bob'
select LEFT(@fullName, CHARINDEX(',', @fullName) - 1) as NameLast,
STUFF(@fullName, 1, CHARINDEX(',', @fullName), '') as NameFirst
You would need to add extra logic to handle any cases where it's not exactly "lastname, firstname".
May 20, 2010 at 12:04 am
If you have a lot of records to split, you might like to look at a range of 'split' functions:
http://florianreischl.blogspot.com/2009/09/high-performance-string-split-functions.html
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 20, 2010 at 8:14 am
Ok, so this worked perfectly:
declare @fullName varchar(30)
SELECT @fullName = Name1
FROM UBMAST
select LEFT(@fullName, CHARINDEX(',', @fullName) - 1) as NameLast,
STUFF(@fullName, 1, CHARINDEX(',', @fullName), '') as NameFirst
The only question I have now. This gave me one record; however, I should have 67,000 records. How do I do this for I get every records in my table?
Thanks,
Jordon
May 20, 2010 at 10:35 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply