May 8, 2009 at 12:05 pm
I have a table that has a field name with data as "Doe,John A" and want to make into 3 rows.
Calling them FName, MInital and LName. I can ger the middle initale and last name with right and left.
But can't get the first name.
I don't want to DECLARE a FName or so on?
Can someone help me.
A coworker gave me this below, but I am still having problems with some names.
Msg 537, Level 16, State 2, Line 5
Invalid length parameter passed to the LEFT or SUBSTRING function.
May 8, 2009 at 12:11 pm
Splitting the string into pieces is the easy part. Making sure you have the right part going into the right field is the tricky part.
The way to split it up is with a "String Parser", using a Tally/Numbers table. This article shows you how to do that: http://qa.sqlservercentral.com/articles/T-SQL/62867/
The question is, have you made sure that all names you need to split are in the same format?
Do you have any entries like "Dr Smith, John", or "John Jacob Jingleheimer Schmidt", or "Johson, Billy-Bob"? Any of those might mess up a simple string split.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 11, 2009 at 2:03 pm
No the data is more like this:
CULLER,PETER O
FRAZIER,BRANDON
MCNEAL,CHRIS Z
LIBBY,JOHN L
BASS,BILL
I just want to split up the data, so I can use it in Reporting Services.
FName+' '+MName+' '+LName AS Employee
Make it easer to read in the report I create.
I can get the last name by using a SUBSTRING,
SUBSTRING([FullName], 1, CHARINDEX(',', [FullName] )-1) AS LName
Thank You.
May 11, 2009 at 2:33 pm
Like GSquared said, this could break if your data is not in the same format, but if your data is clean enough, you may be able to do something like this.
DECLARE @Tmp TABLE (NME VARCHAR(50))
INSERT INTO @Tmp
SELECT 'CULLER,PETER O' UNION
SELECT 'FRAZIER,BRANDON' UNION
SELECT 'MCNEAL,CHRIS Z' UNION
SELECT 'LIBBY,JOHN L' UNION
SELECT 'BASS,BILL'
SELECT SUBSTRING(NME, 1, CHARINDEX(',', NME )-1) AS LName,
CASE WHEN CHARINDEX(' ',REVERSE(NME)) = 2
THEN
LEFT(REVERSE(NME),1)
ELSE '' END MI,
CASE WHEN CHARINDEX(' ',REVERSE(NME)) = 2
THEN
SUBSTRING(NME,CHARINDEX(',', NME )+1,
CHARINDEX(' ',NME)-CHARINDEX(',',NME))
ELSE
SUBSTRING(NME,CHARINDEX(',', NME )+1,LEN(NME))
END FName
FROM @Tmp
May 12, 2009 at 6:32 am
That seems to be working just as this:
SELECT SUBSTRING(NAME, 1, CHARINDEX(',', NAME )-1) AS LName,
CASE WHEN CHARINDEX(' ',REVERSE(NAME)) = 2
THEN
LEFT(REVERSE(NAME),1)
ELSE '' END MI,
CASE WHEN CHARINDEX(' ',REVERSE(NAME)) = 2
THEN
SUBSTRING(NAME,CHARINDEX(',', NAME )+1,
CHARINDEX(' ',NAME)-CHARINDEX(',',NAME))
ELSE
SUBSTRING(NAME,CHARINDEX(',', NAME )+1,LEN(NAME))
END FName
But I am getting a error:
Msg 537, Level 16, State 2, Line 2
Invalid length parameter passed to the LEFT or SUBSTRING function.
This is the Table info for Name.
NAME (varchar(26),null)
May 12, 2009 at 7:28 am
This is what I came up with. It will only work reliably on names in the formats you listed.
-- Set up test data
create table #T (
ID int identity primary key,
Name varchar(100));
-- Test Data
insert into #T (Name)
select 'CULLER,PETER O' union all
select 'FRAZIER,BRANDON' union all
select 'MCNEAL,CHRIS Z' union all
select 'LIBBY,JOHN L' union all
select 'BASS,BILL';
-- Select
select ID, name,
-- First Name
case
when charindex(' ', rtrim(name)) > 0 then
substring(name, charindex(',', name)+1, charindex(' ', name)-charindex(',', name))
else substring(name, charindex(',', name)+1, len(name))
end + ' ' +
-- Middle Initial
case
when charindex(' ', rtrim(name)) > 0
then right(name, 1) + ' '
else ''
end +
-- Last Name
left(name, charindex(',', name, 0)-1) as ProperName
from #T;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 12, 2009 at 7:46 am
Dakotah (5/12/2009)
That seems to be working just as this:SELECT SUBSTRING(NAME, 1, CHARINDEX(',', NAME )-1) AS LName,
CASE WHEN CHARINDEX(' ',REVERSE(NAME)) = 2
THEN
LEFT(REVERSE(NAME),1)
ELSE '' END MI,
CASE WHEN CHARINDEX(' ',REVERSE(NAME)) = 2
THEN
SUBSTRING(NAME,CHARINDEX(',', NAME )+1,
CHARINDEX(' ',NAME)-CHARINDEX(',',NAME))
ELSE
SUBSTRING(NAME,CHARINDEX(',', NAME )+1,LEN(NAME))
END FName
But I am getting a error:
Msg 537, Level 16, State 2, Line 2
Invalid length parameter passed to the LEFT or SUBSTRING function.
This is the Table info for Name.
NAME (varchar(26),null)
You are probably getting an error on the LName column since we are not doing any checking. This means one of your names is not in the correct format. Try something like this.
DECLARE @Tmp TABLE (NME VARCHAR(50))
INSERT INTO @Tmp
SELECT 'CULLER,PETER O' UNION
SELECT 'FRAZIER,BRANDON' UNION
SELECT 'MCNEAL,CHRIS Z' UNION
SELECT 'LIBBY,JOHN L' UNION
SELECT 'BASS,BILL' UNION
SELECT 'BASS' UNION
SELECT 'John Jacob Jingleheimer Schmidt'
SELECT
CASE WHEN CHARINDEX(',',REVERSE(NME)) > 0
THEN
SUBSTRING(NME, 1, CHARINDEX(',', NME )-1)
ELSE '' END AS LName,
CASE WHEN CHARINDEX(' ',REVERSE(NME)) = 2
THEN
LEFT(REVERSE(NME),1)
ELSE '' END MI,
CASE WHEN CHARINDEX(' ',REVERSE(NME)) = 2
THEN
SUBSTRING(NME,CHARINDEX(',', NME )+1,
CHARINDEX(' ',NME)-CHARINDEX(',',NME))
ELSE
SUBSTRING(NME,CHARINDEX(',', NME )+1,LEN(NME))
END FName
FROM @Tmp
May 12, 2009 at 8:27 am
Hi!
This should be a moot point since it is violating common normalisation. The field contains more then one data value and should not have to be split up to be able to be processed.
May 12, 2009 at 9:30 am
I got it to work:
SELECT [NAME],
SUBSTRING([NAME], CHARINDEX(',', [NAME]) + 1, LEN([NAME])) AS FName,
SUBSTRING([NAME], 1, CHARINDEX(',', [NAME] )-1) AS LName
FROM dbo.MyTable
I would have liked 3 rows, but 2 will work.
Thanks for your help and time with this issue.
May 12, 2009 at 11:01 am
I just want to add my thanks for this info. I have to fix names a lot in my job and I have wondered how I would do it in SQL. Now I have my answer.
As to why would you want to do this, I can give one good reason. I work in a mail house and we have clients that send in names in the format DOE, JOHN J and absolutely insist that the name on the mail piece be in the format John J Doe. So you gotta do it.
And GSquared is right. The real problem is always that small percentage of names that aren't in the expected format. SMITH JR, JOHN L is not a problem, but what if it is SMITH, JOHN L JR instead.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply