Solved

parse a name with commas sql server

Posted on 2011-02-26
12
849 Views
Last Modified: 2012-05-11
Hi,
I have looked at the other examples in EE but am still stumped with parsing the following:

entire_name =
SMITH,JAMES B
JONES,BETTY ANN
HOWELL,THURSTON B,III
PERSON,HUMAN A,JR

I need to parse this out into last_name, first_name, middle
I can get the last  name with a charindex -1 from the comma.
I'm having (mental) problems with the substring for the  first name and then with dealing with the comma and name suffix at the end (e.g. JR or III).

your sql help is greatly appreciated!

thanks
0
Comment
Question by:fhcdaver
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +1
12 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 34989178
is this awhole string or each line separate strings?
0
 

Author Comment

by:fhcdaver
ID: 34989305
the example is a series of separate strings (found in separate rows).  
add to the examples,

DI GIORGI,ANTONIO B
0
 
LVL 26

Expert Comment

by:tigin44
ID: 34989354
declare @fullName varchar(100);
declare @firstName      varchar(30);
declare @lastName      varchar(30);
declare @initName      varchar(30);

--set @fullName = 'HOWELL,THURSTON B,III'
set @fullName = 'JONES,BETTY ANN'


select @lastName = LEFT(@fullName, CHARINDEX(',', @fullName) - 1)
select @fullName = RIGHT(@fullName, LEN(@fullName) - CHARINDEX(',', @fullName))

if CHARINDEX(',', @fullName) > 0
begin
      select @firstName = LEFT(@fullName, CHARINDEX(',', @fullName) - 1)
      select @initName = RIGHT(@fullName, LEN(@fullName) - CHARINDEX(',', @fullName))
end
else
      select @firstName = @fullName
      

select @lastName AS lastName, @firstName AS firstName, ISNULL(@initName, '') AS initName
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 41

Expert Comment

by:Sharath
ID: 34989492
If possible, can you create a function like this.
CREATE FUNCTION [dbo].[fn_Split](@text nvarchar(max), @delimiter char(1) = ' ')
RETURNS @Strings TABLE (position int IDENTITY PRIMARY KEY, value nvarchar(max)) AS 
  BEGIN 
DECLARE @index int
    SET @index = -1
  WHILE (LEN(@text) > 0)
  BEGIN
    SET @index = CHARINDEX(@delimiter , @text)
     IF (@index = 0) AND (LEN(@text) > 0)
  BEGIN 
 INSERT INTO @Strings VALUES (@text)
  BREAK 
    END
     IF (@index > 1)
  BEGIN
 INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
    SET @text = RIGHT(@text, (LEN(@text) - @index))
    END 
   ELSE 
    SET @text = RIGHT(@text, (LEN(@text) - @index))
    END
 RETURN
    END

Open in new window

Use this function in your code.
select col,
       MAX(case rn when 1 then str1 end) LastName,
       MAX(case rn when 2 then value end) FirstName,
       MAX(case rn when 3 then value end) MiddleName,
       MAX(case rn when 4 then str1 end) Suffix
  from (
select *,ROW_NUMBER() over (partition by col order by pos1,position) rn
  from (
select col,position pos1, value str1 
  from your_table
 cross apply dbo.fn_Split(col,',')) t1
 cross apply dbo.fn_Split(str1,' ')) t2
group by col

Open in new window

This is how I tested.
declare @table table(col varchar(100))
insert @table values ('SMITH,JAMES B'),('JONES,BETTY ANN'),('HOWELL,THURSTON B,III'),('PERSON,HUMAN A,JR')
select col,
       MAX(case rn when 1 then str1 end) LastName,
       MAX(case rn when 2 then value end) FirstName,
       MAX(case rn when 3 then value end) MiddleName,
       MAX(case rn when 4 then str1 end) Suffix
  from (
select *,ROW_NUMBER() over (partition by col order by pos1,position) rn
  from (
select col,position pos1, value str1 
  from @table
 cross apply dbo.fn_Split(col,',')) t1
 cross apply dbo.fn_Split(str1,' ')) t2
group by col
/*
col	LastName	FirstName	MiddleName	Suffix
HOWELL,THURSTON B,III	HOWELL	THURSTON	B	III
JONES,BETTY ANN	JONES	BETTY	ANN	NULL
PERSON,HUMAN A,JR	PERSON	HUMAN	A	JR
SMITH,JAMES B	SMITH	JAMES	B	NULL
*/

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34991881
you could do it this way if the names are less than 2048 characters long... and you don't need to worry about
extra embedded commas in the first/middle name portion...

replace fullname in the partition by with the primary key of the row....
Select Fullname ,[1] as Surname,coalesce([2],'') as Firstname
                , coalesce([3],'') as Middle, coalesce([4],'') as Suffix
 From (
Select Fullname,substring(Xname+',',v.number,charindex(',',Xname+',',v.number)-v.number) as part 
       ,ROW_NUMBER() over (partition by fullname order by v.number asc) as rn
  From (
Select Fullname,coalesce(LTRIM(rtrim(replace(replace(fullname,'  ',' '),' ',','))),',') as Xname
from 
(
Select 'SMITH,JAMES B' as fullname
union all select 'JONES,BETTY ANN'
union all select 'HOWELL,THURSTON B,III'
union all select 'PERSON,HUMAN A,JR'
union all select null

) as x
) as x
Cross Join Master.dbo.spt_values as v
Where v.type='p'
and v.number between 1 and len(x.xname)
and SUBSTRING(','+x.xname,v.number,1)=','
) as P
Pivot (max(part) for rn in ([1],[2],[3],[4])) as pvt
order by fullname

Open in new window

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 125 total points
ID: 34991884
line 7 - 17 would become

select x.* ,coalesce(LTRIM(rtrim(replace(replace(fullname,'  ',' '),' ',','))),',') as Xname
from yourtable as x
0
 

Author Comment

by:fhcdaver
ID: 34992683
Sharath:  I tried your suggestion and get

Msg 321, Level 15, State 1, Line 13
"col" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

as an error.  I'm running SQL server 2008 R2.  If i change the "col" to a name on the table i'm using (e.g. "Name_all") it says the same thing (Name_all is not a recognized...etc)

0
 

Author Comment

by:fhcdaver
ID: 34992735
Update:
I set compatibility_level to 90 and both Sharath and lowfatspread's suggestions were much improved.   I need to play with lowfatspread's.  I'd love that to be a function.
Sharatth, your function bombs with an "invalid length parameter passed to the RIGHT function"  

That could be because not all names in the table have a comma....

dave
0
 

Author Comment

by:fhcdaver
ID: 34992754
Lowfatspread:  It's real close.  In my table (which has about 6000 names) I have people with names thus:

JONES JR,JAMES B

Your code parses it to be:

last name= JONES
first name = JR
middle = JAMES
suffix = B

can you tweak this?
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 125 total points
ID: 34994983
>> "col" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.
Replace col with your actual column name.

>> Sharatth, your function bombs with an "invalid length parameter passed to the RIGHT function"
Could you post such data. I have checked with names having no comma and the function works fine.
declare @table table(col varchar(100))
insert @table values ('SMITH,JAMES B'),('JONES,BETTY ANN'),('HOWELL,THURSTON B,III'),('PERSON,HUMAN A,JR'),
                     ('Name without comma'),('JONES JR,JAMES B')
select col,
       MAX(case rn when 1 then value end) LastName,
       MAX(case rn when 2 then value end) FirstName,
       MAX(case rn when 3 then value end) MiddleName,
       MAX(case rn when 4 then value end) Suffix
  from (
select *,ROW_NUMBER() over (partition by col order by pos1,position) rn
  from (
select col,position pos1, value str1 
  from @table
 cross apply dbo.fn_Split(col,',')) t1
 cross apply dbo.fn_Split(str1,' ')) t2
group by col
/*
col	LastName	FirstName	MiddleName	Suffix
HOWELL,THURSTON B,III	HOWELL	THURSTON	B	III
JONES JR,JAMES B	JONES	JR	JAMES	B
JONES,BETTY ANN	JONES	BETTY	ANN	NULL
Name without comma	Name	without	comma	NULL
PERSON,HUMAN A,JR	PERSON	HUMAN	A	JR
SMITH,JAMES B	SMITH	JAMES	B	NULL
*/

Open in new window

0
 

Author Comment

by:fhcdaver
ID: 34998563
Hi Sharath,

Posting the data would be a confidentiality issue with my client.  However, the sampels you have are  perfectly legitimate.  

What I did was change the select in your sample to point to my table (instead of from @table I use from my_table, and the column on my_table is called name_all and it's an nvarchar(20).  

I looked at the data, found no nulls, and no (as best I can tell) invalid chartacters.

0
 

Author Closing Comment

by:fhcdaver
ID: 35150689
the code suggested by both Sharath and lowfatspread was helpful in my writing a select to met my needs.
thankyou
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Tracking Problematic Page Splits 1 50
grouping by date only 6 22
Create a Calendar table 29 45
Sql Function to get the word position 20 19
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question