Native SQL to Parse Prefix First Middle Last Suffix from full Name without platform dependent functions
$10-30 USD
已关闭
已发布将近 7 年前
$10-30 USD
货到付款
Parse a full name in the format, prefix first middle last suffix. Not all parts may be included. Most commonly there may be no middle name, prefix, or suffix. I want to be able to accomplish this in SQL not using any platform dependent functions so it can be a platform independent bridge. I tried some code but the suffix Jr. gets stuffed in with the last name. It may be that a web service for data quality or machine learning like Melissa would work better. The example includes data.
SELECT
FIRST_NAME.INPUT_DATA
,[login to view URL]
,FIRST_NAME.FIRST_NAME
,CASE WHEN 0 = CHARINDEX(' ',[login to view URL])
THEN NULL --no more spaces found, consider remaining to be last name
ELSE SUBSTRING(
[login to view URL]
,1
,CHARINDEX(' ',[login to view URL])-1
)
END AS MIDDLE_NAME
,SUBSTRING(
[login to view URL]
,1 + CHARINDEX(' ',[login to view URL])
,LEN([login to view URL])
) AS LAST_NAME
FROM
(
SELECT
[login to view URL]
,CASE WHEN 0 = CHARINDEX(' ',[login to view URL])
THEN [login to view URL] --no space found, return the entire string
ELSE SUBSTRING(
[login to view URL]
,1
,CHARINDEX(' ',[login to view URL])-1
)
END AS FIRST_NAME
,CASE WHEN 0 = CHARINDEX(' ',[login to view URL])
THEN NULL --no spaces found, consider to be first name
ELSE SUBSTRING(
[login to view URL]
,CHARINDEX(' ',[login to view URL])+1
,LEN([login to view URL])
)
END AS REMAINING
,PREFIX.INPUT_DATA
FROM
(
SELECT --CLEAN_DATA
--if first three characters match list,
--parse as a "PREFIX". else return NULL for PREFIX.
CASE WHEN SUBSTRING(CLEAN_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS')
THEN LTRIM(RTRIM(SUBSTRING(CLEAN_DATA.FULL_NAME,1,3)))
ELSE NULL
END AS PREFIX
,CASE WHEN SUBSTRING(CLEAN_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS')
THEN LTRIM(RTRIM(SUBSTRING(CLEAN_DATA.FULL_NAME,4,LEN(CLEAN_DATA.FULL_NAME))))
ELSE LTRIM(RTRIM(CLEAN_DATA.FULL_NAME))
END AS REMAINING
,CLEAN_DATA.INPUT_DATA
FROM
(
SELECT
--trim leading & trailing spaces to prepare for processing
--replace extra spaces in name
REPLACE(REPLACE(LTRIM(RTRIM(FULL_NAME)),' ',' '),' ',' ') AS FULL_NAME
,FULL_NAME AS INPUT_DATA
FROM
(
--test with test data, or table
--table
--SELECT CONTACT AS FULL_NAME
--FROM CONTACT
--test data
--/*
SELECT 'Andy D Where' AS FULL_NAME
UNION SELECT 'Cathy T Landers' AS FULL_NAME
UNION SELECT 'Ms Annie Wint There' AS FULL_NAME
UNION SELECT 'Frank Fields' AS FULL_NAME
UNION SELECT 'Howdy U Pokes Jr.' AS FULL_NAME
--*/
) SOURCE_DATA
) CLEAN_DATA
) PREFIX
) FIRST_NAME
Hello,
I am SQL developer having more than 8 years of experience. I can give you solution and I am available during your time.
Please give me sample data and expected result and I will definitely guve you solution.
Regards,
Shree