Dave Roger
asked on
parse a name with commas sql server
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
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
is this awhole string or each line separate strings?
ASKER
the example is a series of separate strings (found in separate rows).
add to the examples,
DI GIORGI,ANTONIO B
add to the examples,
DI GIORGI,ANTONIO B
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
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
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
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
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
*/
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....
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
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)
ASKER
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
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
ASKER
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?
JONES JR,JAMES B
Your code parses it to be:
last name= JONES
first name = JR
middle = JAMES
suffix = B
can you tweak this?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
the code suggested by both Sharath and lowfatspread was helpful in my writing a select to met my needs.
thankyou
thankyou