|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Parsing spacesHow do I parse out words from a field? Specifically I have a field which
contains the full name of someone: first, middle, and initial. These words are all separated by spaces like so: John Abraham Adams How do I get just the first name "John" in a separate field and then get "Adams" in another separate field? "Ryan D" <Ry***@discussions.microsoft.com> wrote in message Take a look at the CHARINDEX function. Using that with SUBSTRING, LEFT or news:5A4D29E0-458E-4A3A-A973-5BA3475A68B6@microsoft.com... > How do I parse out words from a field? Specifically I have a field which > contains the full name of someone: first, middle, and initial. These > words > are all separated by spaces like so: > John Abraham Adams > > How do I get just the first name "John" in a separate field and then get > "Adams" in another separate field? RIGHT will get you what you need. A couple of questions. What happens if they have titles and surnames. Like John Abraham Smith Jr., or a last name like: Van Dyke Some food for thought. Rick Sawtell MCT, MCSD, MCDBA Thank you Rick, I am able to parse the first name now. But how do I parse
the last name? Is there a way to use the substring command starting from the end of the line and go backwards? I don't understand where the RIGHT comes into play. You're right, I'm not taking into account Jr. or titles. I'll worry about that later. Show quote "Rick Sawtell" wrote: > > "Ryan D" <Ry***@discussions.microsoft.com> wrote in message > news:5A4D29E0-458E-4A3A-A973-5BA3475A68B6@microsoft.com... > > How do I parse out words from a field? Specifically I have a field which > > contains the full name of someone: first, middle, and initial. These > > words > > are all separated by spaces like so: > > John Abraham Adams > > > > How do I get just the first name "John" in a separate field and then get > > "Adams" in another separate field? > > Take a look at the CHARINDEX function. Using that with SUBSTRING, LEFT or > RIGHT will get you what you need. > > A couple of questions. > > What happens if they have titles and surnames. Like John Abraham Smith Jr., > or a last name like: Van Dyke > > > Some food for thought. > > > Rick Sawtell > MCT, MCSD, MCDBA > > > > CREATE TABLE #foo
( fullname VARCHAR(32) ); INSERT #foo SELECT 'John Fitzgerald Kennedy'; INSERT #foo SELECT 'Aaron M.R. Bertrand'; INSERT #foo SELECT 'Apu Nahasapeemapetilon'; INSERT #foo SELECT 'Jon Woo'; SELECT FirstName = LEFT(fullname, CHARINDEX(' ',fullname)-1), LastName = RIGHT(fullname, CHARINDEX(' ', REVERSE(fullname))-1) FROM #foo; DROP TABLE #foo; Of course, once you take prefixes and suffixes into account, as well as dealing with missing middle names, your money would probably be better spent on a data scrubbing software package than on all the time it's going to take you to develop a satisfactory solution... Show quote "Ryan D" <Ry***@discussions.microsoft.com> wrote in message news:2889BF1E-43CB-4BD9-A988-003428BA0B9E@microsoft.com... > Thank you Rick, I am able to parse the first name now. But how do I parse > the last name? Is there a way to use the substring command starting from > the > end of the line and go backwards? I don't understand where the RIGHT > comes > into play. > > You're right, I'm not taking into account Jr. or titles. I'll worry about > that later. The other day, a guy posted a link to some assorted user defined functions
they had written. Most of them are for string parsing. http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115 Show quote "Ryan D" <Ry***@discussions.microsoft.com> wrote in message news:5A4D29E0-458E-4A3A-A973-5BA3475A68B6@microsoft.com... > How do I parse out words from a field? Specifically I have a field which > contains the full name of someone: first, middle, and initial. These > words > are all separated by spaces like so: > John Abraham Adams > > How do I get just the first name "John" in a separate field and then get > "Adams" in another separate field? JT,
This link is helpful, but I would like to do this without creating functions. Isn't there a way to do this in T-SQL? Show quote "JT" wrote: > The other day, a guy posted a link to some assorted user defined functions > they had written. Most of them are for string parsing. > http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115 > > "Ryan D" <Ry***@discussions.microsoft.com> wrote in message > news:5A4D29E0-458E-4A3A-A973-5BA3475A68B6@microsoft.com... > > How do I parse out words from a field? Specifically I have a field which > > contains the full name of someone: first, middle, and initial. These > > words > > are all separated by spaces like so: > > John Abraham Adams > > > > How do I get just the first name "John" in a separate field and then get > > "Adams" in another separate field? > > > > This link is helpful, but I would like to do this without creating Why?> functions. > Isn't there a way to do this in T-SQL? Sure, put the T-SQL into functions. Then it's much less hairy.Thanks JT! I found a function that helped me out on the syntax. Without
creating a new function, I used the REVERSE, SUBSTRING, and CHARINDEX commands to parse out the last name! Show quote "JT" wrote: > The other day, a guy posted a link to some assorted user defined functions > they had written. Most of them are for string parsing. > http://www.universalthread.com/wconnect/wc.dll?LevelExtreme~2,54,33,27115 > > "Ryan D" <Ry***@discussions.microsoft.com> wrote in message > news:5A4D29E0-458E-4A3A-A973-5BA3475A68B6@microsoft.com... > > How do I parse out words from a field? Specifically I have a field which > > contains the full name of someone: first, middle, and initial. These > > words > > are all separated by spaces like so: > > John Abraham Adams > > > > How do I get just the first name "John" in a separate field and then get > > "Adams" in another separate field? > > >  |
|||||||||||||||||||||||
Other interesting topics