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 |
|
tjvinz
Starting Member
5 Posts |
 Posted - 2007-03-21 : 18:30:48
|
| I have a table named conversion. In this table there is not a field for "first name", but only a field for "last name" and "Contact Name". The "Contact Name" holds the full name (First and Last), and the "Last Name" holds the last name.In theory I was thinking that I could take the "Last Name" field and subtract the amount of characters + 1 additional (to account for space in between Contact Name Field - FirstName LastName) and then I would have the first name?I can find the amount of characters in a field name by using this:SELECT LEN(lastname_column) AS 'Length', lastname_columnFROM conv |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-03-21 : 18:53:44
|
[code]select ContactName, replace(ContactName, LastName, '') as FirstName, LastNamefrom conv[/code]
KH |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-03-21 : 19:20:10
|
| Creative, but I can think of two literary characters it would choke on:Humbert Humbert (Nabokov) and Major Major (Heller).e4 d5 xd5 Nf6 |
|
|
|
tjvinz
Starting Member
5 Posts |
Posted - 2007-03-21 : 21:25:29
|
| khtan,That worked perfectly! Wow. You are awesome! Thanks! |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-03-21 : 21:55:24
|
[code]select ContactName, ltrim(stuff(ContactName, charindex(LastName, ContactName), len(LastName), '')) as FirstName, LastNamefrom ( select 'Humbert Humbert (Nabokov)' as ContactName, 'Humbert' as LastName union all select 'Major Major (Heller)' as ContactName, 'Major' as LastName) n[/code]
KH |
|
|
|
|
|
|
|
|
Query Question