February 6, 2018 at 11:46 am
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
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
February 6, 2018 at 12:47 pm
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
February 6, 2018 at 1:02 pm
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
February 6, 2018 at 1:11 pm
drew.allen - Tuesday, February 6, 2018 1:02 PMAnd 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
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
February 6, 2018 at 1:52 pm
usererror - Tuesday, February 6, 2018 1:11 PMdrew.allen - Tuesday, February 6, 2018 1:02 PMAnd 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.
February 7, 2018 at 7:16 am
sestell1 - Tuesday, February 6, 2018 1:52 PMThere 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
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply