splitting a string
-
HI I have a column ful name in the database and i have to split into three fileds first name,last name and middle name.
FullName:
Johnson, kate L
Boje, timothy W
I need to split the above strings as
Fist_name last_name middle_name
johnson kate L
Boje timothy W
SELECT full_name,SUBSTRING(full_name,1,CHARINDEX(',',full_name)-1) as first_name,
SUBSTRING(LTRIM(RTRIM(SUBSTRING(full_name,CHARINDEX(',',full_name)+1,LEN(full_name)))),1,CHARINDEX('$',REPLACE(LTRIM(RTRIM(SUBSTRING(full_name,CHARINDEX(',',full_name)+1,LEN(full_name)))),' ','$'))) as last_name,
SUBSTRING(REPLACE(LTRIM(RTRIM(SUBSTRING(full_name,CHARINDEX(',',full_name)+1,LEN(full_name)))),' ','&'),CHARINDEX('&',REPLACE(LTRIM(RTRIM(SUBSTRING(full_name,CHARINDEX(',',full_name)+1,LEN(full_name)))),' ','&'))+1,LEN(REPLACE(LTRIM(RTRIM(SUBSTRING(full_name,CHARINDEX(',',full_name)+1,LEN(full_name)))),' ','&'))) AS middle_name
FROM names
I am using the above script to do this.
But is there any better way of achieving this?
Thanks.
-
Is first name always guaranteed to be there? If not, what happens to the comma? Is Middle Initial always guaranteed to be there? Can the last name have a space in it as in St. Jean, etc?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Not withstanding Jeff's comments
if all the names are the same format
PARSENAME(REPLACE(REPLACE(full_name,',',''),' ','.'),2) AS [First_Name],
PARSENAME(REPLACE(REPLACE(full_name,',',''),' ','.'),1) AS [Middle_Name],
PARSENAME(REPLACE(REPLACE(full_name,',',''),' ','.'),3) AS [Last_Name]
Far away is close at hand in the images of elsewhere.
Anon. -
Did you hear such name as "Jean-Claude Van Damme"?
Try to work it out.
After you finish with it start with his original name: "Jean-Claude Camille François Van Varenberg"
_____________
Code for TallyGenerator -
LMAOROTF.
Seriously, this is the kind of process you automate as much as possible, but in the end you always have to manually validate the results.
-
>>After you finish with it start with his original name: "Jean-Claude Camille François Van Varenberg"
Leave it to Serqiy to know something like that
But, both he and Remi are absolutely correct.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If every name were:
lastname comma space firstname space initial.
All you would need to do is create a variable @thename:
1. put the fullname in there
2. copy all upto the comma into @lastname
3. put the left over into the @thename
4. reverse the string and count the characters upto the first space. If it's equal to 1, put it into @midinitial,
5. take whatever is left over and put it into @firstname
6. use @firstname, @midinitial, @lastname however you need.
-SQLBill
-
Jeff,
I did not ask about born name of Pele.
Bill,
All he needs to do is:
1) store all parts of names in database separately;
2) store all parts of names in database separately;
3) store all parts of names in database separately;
4) store all parts of names in database separately;
5) store all parts of names in database separately;
6) use ID instead of FullName to reference the person in all tables but "Person".
_____________
Code for TallyGenerator -
Heh... Pele, huh?
Anyway, I agree... if you are in control of the source of the data, then "store all parts of names in databases separately". However, the story changes a bit
when some 3rd party sends you data in a "less than optimal format" 
like the name examples in this thread.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Does not matter.
Whatever whoever sends to you turn it into actual data when INSERT, not when SELECT.
If you (with your code) cannot understand how to convert this s..t they sent into data report validation error and request properly formatted values.
_____________
Code for TallyGenerator -
Sure, sure.... but we both know that they don't always comply... yep, it's crap but sometimes (like in the telephone reseller business) they send you crap just to make your life difficult
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Don't tell me about what they send.
I deal with it every day.
But it does not mean I let this mess into my database. If I cannot work it out during the import nothing is coming in until the issue is sorted out.
And believe me, it works.
_____________
Code for TallyGenerator
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply