August 16, 2011 at 10:53 am
I have a concatenated field with name which I'm trying to parse out. The names are entered as so:
Lastname, Firstname
or sometimes...
Lastname, Firstname Middleinit (notice the space between the firstname and middleinit)
In order to parse out the name, I have the following:
SELECT LTRIM(RIGHT(@Name, CHARINDEX(',',REVERSE((@Name))-1)) --TO FIND FIRSTNAME
SELECT LEFT((@Name, CHARINDEX(',', (@Name)-1) --TO FIND LASTNAME
But the code for the first name will only pickup the correct firstname in cases where there's been no initial entered. How do I select out the substring between the comma and the space after the firstname (so it doesn't matter if there's a middle initial entered or not)?
For example: "MOUSE, MICKEY X" or "MOUSE, MICKEY" = MICKEY MOUSE
Help?
Thank you!!
August 16, 2011 at 12:44 pm
You could use Jeff Moden's awesome splitter[/url] like this:
declare @Name varchar(50) = 'MOUSE, MICKEY X'
SELECT Item as FirstName, Left(@Name, CHARINDEX(',', @Name) - 1) as LastName
from dbo.DelimitedSplit8K(LTRIM(RIGHT(@Name, CHARINDEX(',', REVERSE(@Name)) - 1 )), ' ')
where ItemNumber = 1
That article will explain the function and there is a ton of other incredibly useful information in that article as well.
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 16, 2011 at 1:00 pm
When I've run into this situation, I've split the string and then used a case statement:
DECLARE @NAME varchar(50)
DECLARE @FNAMESTUB varchar(50)
SET @NAME = 'MOUSE, MICKEY X'
SET @FNAMESTUB = LTRIM(SUBSTRING (@NAME,CHARINDEX(',',@name)+1,LEN(@NAME) - CHARINDEX(',',@name)))
--FirstName
SELECT SUBSTRING (@NAME,1,CHARINDEX(',',@name) -1)
--LastName
SELECT LASTNAME =
CASE CHARINDEX(' ',@FNAMESTUB)
WHEN 0 then RTRIM(SUBSTRING(@FNAMESTUB,1,LEN(@FNAMESTUB)))
ELSE RTRIM(SUBSTRING(@FNAMESTUB,1,LEN(@FNAMESTUB)- (LEN(@FNAMESTUB) -CHARINDEX(' ',@FNAMESTUB))))
END
July 15, 2013 at 12:37 pm
Hi,
I need to parse last name,first name space middle name.
Example A - DOE,JOHN
Example B - DOE,JOHN A
I am able to parse the last name.
left(dbo.AbstractData.Name, charindex(',', dbo.AbstractData.Name)-1) as last name
I can parse the first and middle names together.
ltrim(right( dbo.AbstractData.Name,(len(dbo.AbstractData.Name)-charindex(',',dbo.AbstractData.Name))))
as firstmiddlename
I can parse the middle name.
SUBSTRING(dbo.AbstractData.Name,CHARINDEX(' ',dbo.AbstractData.Name + ' ')+1,LEN(dbo.AbstractData.Name))
I am having trouble parsing the first name. The first name is everything after the comma and before the first blank after the comma (or the end of the string, in which case there is no middle name).
Can anyone help me parse the first name only?
Thanks
July 15, 2013 at 12:48 pm
ajlefort (7/15/2013)
Hi,I need to parse last name,first name space middle name.
Example A - DOE,JOHN
Example B - DOE,JOHN A
I am able to parse the last name.
left(dbo.AbstractData.Name, charindex(',', dbo.AbstractData.Name)-1) as last name
I can parse the first and middle names together.
ltrim(right( dbo.AbstractData.Name,(len(dbo.AbstractData.Name)-charindex(',',dbo.AbstractData.Name))))
as firstmiddlename
I can parse the middle name.
SUBSTRING(dbo.AbstractData.Name,CHARINDEX(' ',dbo.AbstractData.Name + ' ')+1,LEN(dbo.AbstractData.Name))
I am having trouble parsing the first name. The first name is everything after the comma and before the first blank after the comma (or the end of the string, in which case there is no middle name).
Can anyone help me parse the first name only?
Thanks
Please stick to the thread you already started on this.
http://www.sqlservercentral.com/Forums/Topic1473843-8-1.aspx
_______________________________________________________________
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 15, 2013 at 7:13 pm
tacy.highland (8/16/2011)
I have a concatenated field with name which I'm trying to parse out. The names are entered as so:Lastname, Firstname
or sometimes...
Lastname, Firstname Middleinit (notice the space between the firstname and middleinit)
In order to parse out the name, I have the following:
SELECT LTRIM(RIGHT(@Name, CHARINDEX(',',REVERSE((@Name))-1)) --TO FIND FIRSTNAMESELECT LEFT((@Name, CHARINDEX(',', (@Name)-1) --TO FIND LASTNAME
But the code for the first name will only pickup the correct firstname in cases where there's been no initial entered. How do I select out the substring between the comma and the space after the firstname (so it doesn't matter if there's a middle initial entered or not)?
For example: "MOUSE, MICKEY X" or "MOUSE, MICKEY" = MICKEY MOUSE
Help?
Thank you!!
You could use a pattern-splitter string FUNCTION, like PatternSplitCM described in the fourth article in my signature links:
WITH Names (Name) AS (
SELECT 'MOUSE, MICKEY X' UNION ALL SELECT 'MOUSE, MICKEY')
SELECT STUFF((
SELECT ' ' + Item
FROM dbo.PatternSplitCM(Name, '[A-Z]')
WHERE [Matched]=1 AND ItemNumber IN (1,3)
ORDER BY ItemNumber DESC
FOR XML PATH('')), 1, 1, '')
FROM Names
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply