July 11, 2009 at 8:56 am
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
July 11, 2009 at 9:51 am
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
July 19, 2009 at 9:21 pm
Uh huh... and what do you do with names like the following?
Mrs. Betty Joe Lynne Van de Horn MD
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply