June 1, 2013 at 6:25 pm
Hi:
I would like someone can help. I heve a column with many rows like:
NAME Jonh*Smith*Alu
Alex*Campos*kery
Chipper*Jon*Ali
Larry*Walker*kin
AND I WANT TO GET SOME LIKE:
NAME
Jonh Smith Alu
Alex Campos kery
Chipper Jon Ali
Larry Walker kin
Thanks in advance.
June 2, 2013 at 9:05 am
You don't need to split this to achieve the results you are looking for. All you need is a simple replace.
select replace(Name, '*', ' ')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 2, 2013 at 10:19 am
In a simple world, it looks like a good opportunity to use the '*' to split the field into 3 columns:
FName
MName
LName
Gives you much more flexibility for future needs.
June 2, 2013 at 11:40 pm
homebrew01 (6/2/2013)
In a simple world, it looks like a good opportunity to use the '*' to split the field into 3 columns:FName
MName
LName
Gives you much more flexibility for future needs.
Unfortunately, this is not a simple world. It is highly unlikely that this will work properly because not everyone has a middle name. With a simple solution like the one proposed, the last name of such will end up in the middle name column. Admittedly, it's not rocket science to do a little coding to properly split the names and figure out if there is a middle name or not.
It does get more complex, though. What of a name like this:
John*Henderson*Jr
Jr would be placed in the last name...but that's not a name at all.
The integrity of the data entry must be certain, and then the algorithm needs to be well thought out. Again, not rocket science, but...
Dana
June 3, 2013 at 8:37 am
I would think they will have the same problem distinguishing names with the current setup. If the asterisk is a delimiter of some kind, then it could be useful to determine how to split the data. This is just theoretical since we don't know what the real data looks like, or the business needs ..... I just hate seing this kind of thing. Many years ago we went through a very similar cleanup, splitting a single name field into multiple fields.
June 3, 2013 at 10:41 am
homebrew01 (6/3/2013)
I would think they will have the same problem distinguishing names with the current setup. If the asterisk is a delimiter of some kind, then it could be useful to determine how to split the data. This is just theoretical since we don't know what the real data looks like, or the business needs ..... I just hate seing this kind of thing. Many years ago we went through a very similar cleanup, splitting a single name field into multiple fields.
Yes, it's always a difficult thing to deal with. I don't know why it's ever done, especially these days. It's not a new problem, this kind of thing has been a known boo-boo for many, many years...decades. We haven't even touched on the ethnic issues, where some ethnic groups place the surname first.
Years ago one of my first big contract jobs involved taking over and finishing a FoxPro database and application at a collection agency. The previous developers seemed to have broken just about every good-practice rule in the book! They had names stored as in this example, they had blank name fields, they had blank key values (there seemed to be no enforcement of keys at all), they had zip codes stored as numeric values, they had empty city and/or state columns, they had payment history dates that were several years into the future or so far back in time that people's great grandfathers must have made the payments - when they were teenagers, they had empty due dates, they had multiple duplicate records. There didn't seem to be any validation of any columns. And the worst one? They had all this test data in the live system when it wasn't really fit to do business with yet! :blink: It was really difficult trying to fix the problems while the system was in use. You have to deal with what is given to you, but man! The logic was so bad that after a while I told my boss that it was best to rewrite it from scratch. He agreed and told the agency, who didn't care as long as we got it working for them.
Dana
June 3, 2013 at 6:22 pm
Thanks, everyone.
June 3, 2013 at 6:25 pm
Sean Lange (6/2/2013)
You don't need to split this to achieve the results you are looking for. All you need is a simple replace.
select replace(Name, '*', ' ')
Yes, it works !
Thank You.
June 4, 2013 at 7:31 am
Glad that works for now. I have to agree with the sentiment of the others that parsing this into appropriate columns would be best. It is however incredibly difficult to split out names once they are shoved together in a single column.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply