SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Extract Text around comma and space into other columns


Extract Text around comma and space into other columns

Author
Message
Mark Tarquini
Mark Tarquini
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 149
From another post in this site, I got a working query to give me the text on the left and right side of the comma, but now I need to check anything on the far right of a space.


A particular vendor who will rename nameless puts LastName, Firstname<space>Middle Initial all in one data column. I want to update a column called [MBR_LastName] and [MBR_FirstName] with the appropriate data. I'm getting myself confused on how to isolate the first name part. I feel I am close...
If you run the query below it will give you the last name, and the first name with the <space> and middle initial, and then what character # the space is...

Also, the RTRIM is in there because before I was getting all banks for the First Name until I realized there was a ton of blank characters to the right of the first name text.


-------------------------
-- Names Table
-------------------------
Use Workbench2

CREATE TABLE TestNames
(
MBR_NM varchar(255),
MBR_FirstName varchar(50),
MBR_LastName varchar(150)
);

INSERT INTO TestNames (MBR_NM, MBR_FirstName, MBR_LastName)
Values('Smith, John H','','')
INSERT INTO TestNames (MBR_NM, MBR_FirstName, MBR_LastName)
Values('Wayne, John','','')
INSERT INTO TestNames (MBR_NM, MBR_FirstName, MBR_LastName)
Values('Kirk, James T','','')
INSERT INTO TestNames (MBR_NM, MBR_FirstName, MBR_LastName)
Values('Picard, John L','','')
INSERT INTO TestNames (MBR_NM, MBR_FirstName, MBR_LastName)
Values('Riker, William','','')


select
left(MBR_NM, charindex(',', MBR_NM)-1) as LastName,
LTRIM(right(RTRIM(MBR_NM), len(MBR_NM)-charindex(',', MBR_NM))) as FirstName,
charindex(' ',LTRIM(right(RTRIM(MBR_NM), len(MBR_NM)-charindex(',', MBR_NM)))) as [Space Location]
from TestNames

update TestNames
set MBR_LastName = left(MBR_NM, charindex(',', MBR_NM)-1) from TestNames

select * from TestNames


-Mark

MS-SQL 2016 Enterprise, Azure Hosted.
Techie/Sysadmin by trade; completely new to T-SQL. I hardly call myself a DBA. Ive got a lot to learn and doing my best.
sestell1
sestell1
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8991 Visits: 4372
Here's some basic sample code that should get you going.
You'll have to account for edge cases like missing middle initials, extra spaces, prefixes/suffixes, etc...


DECLARE @MBR_NM VarChar(50) = 'Smith, John J'

SELECT
-- Find the position of the first comma in the string
CharIndex(',',@MBR_NM) AS POSITION_FIRST_COMMA,

-- Find the second space in the string.
-- The third parameter of CharIndex lets you specify a starting position.
-- Look for a space, but starting after the position of your comma
-- plus a couple of characters to get past the first space.
CharIndex(' ', @MBR_NM, CharIndex(',',@MBR_NM) + 3) AS POSITION_SECOND_SPACE,

/*
Now combine the two above formulas to break out the pieces you need.
*/

-- First name is everything left of the comma
Left(@MBR_NM, CharIndex(',',@MBR_NM) -1 ) AS Name_Last,

-- Middle name is between the space after the comma and the second space
SubString(@MBR_NM, CharIndex(',',@MBR_NM) + 2, CharIndex(' ', @MBR_NM, CharIndex(',',@MBR_NM) + 3) - (CharIndex(',',@MBR_NM) + 2) ) AS Name_First,

-- Middle initial is one character after the second space
SubString(@MBR_NM, CharIndex(' ', @MBR_NM, CharIndex(',',@MBR_NM) + 3) +1, 1) AS Name_Middle_Initial

drew.allen
drew.allen
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60601 Visits: 16019
And what happens when someone has a first name that includes a space? "Jackson, La Toya"

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Mark Tarquini
Mark Tarquini
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 149
drew.allen - Tuesday, February 6, 2018 1:02 PM
And what happens when someone has a first name that includes a space? "Jackson, La Toya"

Drew

A valid point. It seems then I'd need something that would look for the comma on the left and only a space in the 2nd character position from the right, possibly?


-Mark

MS-SQL 2016 Enterprise, Azure Hosted.
Techie/Sysadmin by trade; completely new to T-SQL. I hardly call myself a DBA. Ive got a lot to learn and doing my best.
sestell1
sestell1
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8991 Visits: 4372
usererror - Tuesday, February 6, 2018 1:11 PM
drew.allen - Tuesday, February 6, 2018 1:02 PM
And what happens when someone has a first name that includes a space? "Jackson, La Toya"

Drew

A valid point. It seems then I'd need something that would look for the comma on the left and only a space in the 2nd character position from the right, possibly?

There could be spaces in the last name as well... combined names (Smith, Jim & Donna)... etc...
Without fixed delimiters you can rely on, I suspect you'll find a never ending list of edge cases and bad data and could spend the rest of your life trying to get 100%.
The best option would be to push back on the vendor to send you the data parsed out instead of combined if you really need the name elements split out.

Mark Tarquini
Mark Tarquini
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 149
sestell1 - Tuesday, February 6, 2018 1:52 PM

There could be spaces in the last name as well... combined names (Smith, Jim & Donna)... etc...
Without fixed delimiters you can rely on, I suspect you'll find a never ending list of edge cases and bad data and could spend the rest of your life trying to get 100%.
The best option would be to push back on the vendor to send you the data parsed out instead of combined if you really need the name elements split out.

I sent an email to the vendor (a fairly large health insurance company) asking if this is feasible. Everyone else does it that we work with.
In the meantime, Excel is quick enough at splitting them out for me, while I learn how to do it with T-SQL.


-Mark

MS-SQL 2016 Enterprise, Azure Hosted.
Techie/Sysadmin by trade; completely new to T-SQL. I hardly call myself a DBA. Ive got a lot to learn and doing my best.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search