Text Parsing Problem

Question

Text Parsing Problem

brekher

SSCommitted

Points: 1514
More actions
July 11, 2009 at 8:56 am

#134247

I currently run a process to import from excel into sql 2k5 through ssis. Once complete, I have to parse the name being that it is going into multiple columns(i.e last_name, first_name, middle_initial, suffix) All is working just fine, however in cases where there is an "Estate of John Doe" for example, I need to look for 'Estate of' in the table and leave that record name alone but need to put a "." into name_first_1. My code is below, any help would be greatly appreciated. Thanks!
update dbo.Import_Stage_CMSVC
set name_last_2 = LAST_NAME
,name_first_2 = FIRST_NAME
,name_mid_init_2 = middle_initial
from(
SELECT
FIRST_NAME.ORIGINAL_INPUT_DATA
,FIRST_NAME.FIRST_NAME
,CASE WHEN 0 = CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)
THEN NULL --no more spaces? assume rest is the last name
ELSE SUBSTRING(
FIRST_NAME.REST_OF_NAME
,1
,CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)-1
)
END AS MIDDLE_NAME
,CASE WHEN 0 = CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)
THEN NULL --no more spaces? assume rest is the last name
ELSE left(SUBSTRING(
FIRST_NAME.REST_OF_NAME
,1
,CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)-1
),1)
END AS MIDDLE_INITIAL
,LTRIM(SUBSTRING(
FIRST_NAME.REST_OF_NAME
,1 + CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)
,LEN(FIRST_NAME.REST_OF_NAME)
)) AS LAST_NAME
FROM
(
SELECT
CASE WHEN 0 = CHARINDEX(' ',TEST_DATA.FULL_NAME)
THEN TEST_DATA.FULL_NAME --No space? return the whole thing
ELSE SUBSTRING(
FULL_NAME
,1
,CHARINDEX(' ',TEST_DATA.FULL_NAME)-1
)
END AS FIRST_NAME
,CASE WHEN 0 = CHARINDEX(' ',TEST_DATA.FULL_NAME)
THEN NULL --no spaces @ all? then 1st name is all we have
ELSE SUBSTRING(
FULL_NAME
,CHARINDEX(' ',TEST_DATA.FULL_NAME)+1
,LEN(TEST_DATA.FULL_NAME)
)
END AS REST_OF_NAME
,TEST_DATA.ORIGINAL_INPUT_DATA
FROM
(
SELECT
--trim leading & trailing spaces before trying to process
--disallow extra spaces *within* the name
REPLACE(LTRIM(RTRIM(FULL_NAME)),' ',' ') AS FULL_NAME
,FULL_NAME AS ORIGINAL_INPUT_DATA
FROM
(
select name_last_2 as full_name from dbo.Import_Stage_CMSVC
) RAW_DATA
) TEST_DATA
) FIRST_NAME) parse, dbo.Import_Stage_CMSVC where name_last_2 = ORIGINAL_INPUT_DATA

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

I have had to do the same kind of thing myself and what we chose to do was, to pre-process the data with a .Net Assembly to split the names, before loading it into SQL Server.

A slight variation would be to load the data into a Cleansing Table and use the CLR from within SQL Server to split the names and load the data to the Destination Table.

Just a thought.

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

Jeff Moden SSC Guru Points: 1004558 More actions · Answer 2

Uh huh... and what do you do with names like the following?

Mrs. Betty Joe Lynne Van de Horn MD

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Text Parsing Problem

Cookies on SQLServerCentral