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.
| Author |
Topic |
|
jung1975
Aged Yak Warrior
503 Posts |
 Posted - 2006-03-30 : 12:15:29
|
| I have a record like John,Doe (1039)David,sill (1111)I would like to grap the last name Doe . How can I do this? there can be more than one space or just one space between e and (. so how can I use Charindex to return the position of last name? |
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2006-03-30 : 12:25:34
|
| Try this:declare @name varchar(50)set @name = 'John,Doe (1039)'SELECT LTRIM(RTRIM(SUBSTRING(@name, CHARINDEX(',', @name) +1, CHARINDEX('(', @name) - CHARINDEX(',', @name) -1))) This assumes that there will always be a comma before the last name, and that there won't be a left parenthesis ("(") until after the last name. |
 |
|
|
jung1975
Aged Yak Warrior
503 Posts |
Posted - 2006-03-30 : 13:02:45
|
| Actually I need to get rid of the middle name as wellJohn,Doe J (1039) -- > DoeI am trying to use substring but it keep return the 0 value |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-03-30 : 13:11:50
|
This will trim off the numeric portion:select left(@Name, charindex('(', @Name+'(')-1)Good luck with the rest, because you are left with something that is not even in a standard "Last, First Middle" format. Where did you get this crappy data? |
|
|
|
|
|
|
|
|
return last name