Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Split a column Expand / Collapse
Author
Message
Posted Saturday, June 1, 2013 6:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 7:46 PM
Points: 11, Visits: 46
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.
Post #1458978
Posted Sunday, June 2, 2013 9:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 13,481, Visits: 12,342
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1459000
Posted Sunday, June 2, 2013 10:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 1:06 PM
Points: 2,829, Visits: 8,479
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.



Post #1459016
Posted Sunday, June 2, 2013 11:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 53, Visits: 137
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
Post #1459080
Posted Monday, June 3, 2013 8:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 1:06 PM
Points: 2,829, Visits: 8,479
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.


Post #1459322
Posted Monday, June 3, 2013 10:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 53, Visits: 137
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! 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
Post #1459387
Posted Monday, June 3, 2013 6:22 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 7:46 PM
Points: 11, Visits: 46


Thanks, everyone.
Post #1459507
Posted Monday, June 3, 2013 6:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 17, 2013 7:46 PM
Points: 11, Visits: 46
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.
Post #1459509
Posted Tuesday, June 4, 2013 7:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 13,481, Visits: 12,342
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1459767
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse