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 |
|
WJHamel
Aged Yak Warrior
651 Posts |
 Posted - 2012-05-09 : 08:54:53
|
Help. I'm using the following:UPDATE ARREST.DBO.[426warrants] SET FNAME = left(name, charindex(',', name)-1),lname=right(name, len(name)-charindex(',', name));to try to get the data from the NAME column split and directed to the FNAME and LNAME columns. The data in the name column looks like "DOE,JOHN" or "DOE,JOHN JAMES" or "DOE,JOHN JAMES Jr". There is never a space after the comma but any data after the group of characters after the comma is always seperated by a space. First issue is that the FNAME, LNAME must be substringed (20 chars for lname, 15 for fname). Not sure how to work the substring into that.thanks |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-05-09 : 09:22:44
|
| What you have posted seems to work correctly. Are you trying to split the parts after the comma into individual pieces? In the examples you posted, what would be the expected output in each case? |
 |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-05-09 : 09:26:58
|
actually, let me clarify. I did figure out the placement of the substring, and i got the LEFT portion to pull out the data i wanted, but i'm having trouble with the RIGHT function in grabbing everything between the comma and the first space, up to 15 characters. That's where i'm stuck currently. Using:UPDATE ARREST.DBO.[426warrants] SET FNAME = Substring((right(name, len(name)-charindex(' ', name))),1,15)produces garbage.so in the case of "DOE,JOHN Jr", my first right pull would be "JOHN". I will then need to figure out the pull between the first and second space. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-05-09 : 09:43:53
|
You can use a combination of STUFF and substring and so on, but the logic gets too complicated, at least for me. An easier way perhaps would be to use a splitter function and then pivot it.You will find several splitter functions if you google for it. One I particularly like is Jeff Moden's code in Fig. 21 of this article: http://www.sqlservercentral.com/articles/Tally+Table/72993/ Copy that code and run it to install the function. Then, use it like in the example below:CREATE TABLE #tmp (id INT, NAME VARCHAR(255));INSERT INTO #tmp VALUES (1,'DOE,JOHN JAMES Jr'),(2,'DOE,JOHN'),(3,'DOE,Jane')SELECT *FROM #tmp CROSS APPLY (SELECT * FROM MASTER.dbo.DelimitedSplit8K(REPLACE(Name, ',', ' '), ' ')) sPIVOT(MAX(Item) FOR ItemNumber IN ([1], [2], [3], [4])) PDROP TABLE #tmp |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-05-09 : 13:21:34
|
| Wow. I concede defeat. I'm going to relegate myself to parsing the name data in an excel sheet as that app is much more adept at handling something like this. how and why could a program like excel be so far ahead of sql in this menial task? Thank you a ton for your help though!! |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-05-09 : 14:26:23
|
quote:
Originally posted by WJHamel
Wow. I concede defeat. I'm going to relegate myself to parsing the name data in an excel sheet as that app is much more adept at handling something like this. how and why could a program like excel be so far ahead of sql in this menial task? Thank you a ton for your help though!!
I specialize in making queries less efficient and more complicated than necessary. I haven not yet won any awards for my efforts, but it is only a matter of time. So someone else might be able to suggest a simpler query to accomplish what you are trying to do.I know I don't have to tell you that Excel and SQL Server are two different products targeted at two different audiences and any overlap in functionality is on the fringes. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-05-09 : 15:20:49
|
quote:
how and why could a program like excel be so far ahead of sql in this menial task?
You misunderstand the purpose of a relational database: it's to store data efficiently and preserve its integrity, not clean up or interpret messy data from other sources that lack data integrity. T-SQL is not a general purpose programming language, and it's not meant to do hardcore parsing or string manipulation. The proper tool for this work is really SSIS, or a dedicated data cleansing tool, although Excel will do 90% of it.BTW, how do you handle the "Jr"? Or the John James? Is James his middle name? What about 2-word names like "Mary Ann"? |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-05-09 : 21:27:29
|
| Sunita: I know that about you. i've been noticing that. ;-)Rob: Honestly, with my primary job being data conversions, it's much more efficient (and given my level of sql skill) to parse these names in excel using a combination of the Text to Columns function and a few formulas. Then create an insert statement back into sql on a unique value. I had one of my bulgarian office mates try to suggest a bunch of charindex substring logic that looped around itself endlessly which resulted in a singularity opening up in the office which swallowed three coworkers. I'll stick with excel for these menial tasks. |
|
|
|
|
|
|
|
|
substring, left, right