January 4, 2011 at 4:02 pm
Question:
I have this code that works great if the name format is standard ( Sample Jr., Robert W. ).
-- Parse FullName(Last Suffix, First Middle)
DECLARE @Fullname varchar(100)
DECLARE @LastName varchar(30)
DECLARE @FirstName varchar(30)
DECLARE @MiddleName varchar(15)
DECLARE @Suffix varchar(10)
SET @FullName = 'Sample Jr., Robert W.'
SELECT
@LastName = LEFT(@FullName , CHARINDEX(' ' , @FullName) - 1) ,
@Suffix = SUBSTRING(@FullName , CHARINDEX(', ' , @FullName) - 3 , CASE
WHEN CHARINDEX(' ' , @FullName , CHARINDEX(', ' , @FullName) - 2) = 0 THEN LEN(@FullName) + 2
ELSE CHARINDEX(' ' , @FullName , CHARINDEX(', ' , @FullName) + 2)
END - CHARINDEX(', ' , @FullName) - 5) ,
@FirstName = SUBSTRING(@FullName , CHARINDEX(', ' , @FullName) + 2 , CASE
WHEN CHARINDEX(' ' , @FullName , CHARINDEX(', ' , @FullName) + 2) = 0 THEN LEN(@FullName) + 1
ELSE CHARINDEX(' ' , @FullName , CHARINDEX(', ' , @FullName) + 2)
END - CHARINDEX(', ' , @FullName) - 2) ,
@MiddleName = RIGHT(@FullName , LEN(@FullName) - CASE
WHEN CHARINDEX(' ' , @FullName , CHARINDEX(', ' , @FullName) + 2) = 0 THEN LEN(@FullName)
ELSE CHARINDEX(' ' , @FullName , CHARINDEX(', ' , @FullName) + 2)
END)
PRINT @LastName
PRINT @FirstName
PRINT @MiddleName
PRINT LTRIM(@Suffix)
However:
If the name is in a different format like:
Sample Jr., Robert W.
Sample, Robert W.
Sample, Robert
Then I obviously get incorrect results.
How can I put this into code where it checks for the format and then uses the correct Parsing code?
Thanks!
Drew
January 5, 2011 at 4:45 pm
I hope this helps somebody. I decided to use this code:
-- Make temp table and loop through for test.
CREATE TABLE #TEMP_FullName
(
ID int IDENTITY(1,1) ,
FullName varchar(100) )
-- Insert Test Data.
INSERT INTO #TEMP_FullName ( FullName ) VALUES ( 'Sample III, John Q.' )
INSERT INTO #TEMP_FullName ( FullName ) VALUES ( 'Sample II, John Q.' )
INSERT INTO #TEMP_FullName ( FullName ) VALUES ( 'Sample JR, John Q.' )
INSERT INTO #TEMP_FullName ( FullName ) VALUES ( 'Sample SR., John Q.' )
INSERT INTO #TEMP_FullName ( FullName ) VALUES ( 'Sample, John Q' )
INSERT INTO #TEMP_FullName ( FullName ) VALUES ( 'Sample, John Q.' )
INSERT INTO #TEMP_FullName ( FullName ) VALUES ( 'Sample, John' )
-- Verify table data was inserted.
SELECT * FROM #TEMP_FullName
-- Declare Variables.
DECLARE @Fullname varchar(100)
DECLARE @LastName varchar(30)
DECLARE @FirstName varchar(30)
DECLARE @MiddleName varchar(15)
DECLARE @Suffix_LastName varchar(30)
DECLARE @SuffixName varchar(10)
DECLARE @RowID int
-- Select initial row.
SELECT @RowID = MIN(ID) FROM #TEMP_FullName
WHILE @RowID IS NOT NULL
BEGIN
-- Clear/reset variables.
SET @FirstName = NULL
SET @Fullname = NULL
SET @LastName = NULL
SET @MiddleName = NULL
SET @SuffixName = NULL
SET @Suffix_LastName = NULL
-- Select Fullname.
SELECT @FullName = FullName FROM #TEMP_FullName WHEREID = @RowID
-- Parse Last, First and Middle name data.
SELECT
@LastName = LEFT(@FullName , CHARINDEX(', ' , @FullName) - 1) ,
@FirstName = SUBSTRING(@FullName , CHARINDEX(', ' , @FullName) + 2 , CASE
WHEN CHARINDEX(' ' , @FullName , CHARINDEX(', ' , @FullName) + 2) = 0 THEN LEN(@FullName) + 1
ELSE CHARINDEX(' ' , @FullName , CHARINDEX(', ' , @FullName) + 2)
END - CHARINDEX(', ' , @FullName) - 2) ,
@MiddleName = RIGHT(@FullName , LEN(@FullName) - CASE
WHEN CHARINDEX(' ' , @FullName , CHARINDEX(', ' , @FullName) + 2) = 0 THEN LEN(@FullName)
ELSE CHARINDEX(' ' , @FullName , CHARINDEX(', ' , @FullName) + 2)
END)
-- Parse suffix from Last name.
IF @LastName LIKE '% %'
BEGIN
SELECT
@Suffix_LastName = LEFT(@LastName , charindex(' ' , @LastName) - 1) ,
@SuffixName = RIGHT(@LastName , charindex(' ' , reverse(@LastName)) - 1)
SET @LastName = @Suffix_LastName
END
ELSE
BEGIN
SET @SuffixName = NULL
END
-- Select Next row.
SELECT @RowID = MIN(ID) FROM #TEMP_FullName WHERE ID > @RowID
END
-- Drop Table.
DROP TABLE #TEMP_FullName
Drew
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply