SELECT MEMBERID, FirstName INTO #Javed FROM (SELECT
memberid,
FullName,
FirstName,
CASE
WHEN 0 = CHARINDEX(' ', REST_OF_NAME) THEN NULL -- no more spaces? assume rest is the last name
ELSE SUBSTRING(REST_OF_NAME, 1, CHARINDEX(' ', REST_OF_NAME) - 1)
END AS MiddleName,
SUBSTRING(REST_OF_NAME, CHARINDEX(' ', REST_OF_NAME) + 1, LEN(REST_OF_NAME)) AS LastName
FROM (
SELECT
memberid,
TITLE,
CASE
WHEN 0 = CHARINDEX(' ', REST_OF_NAME) THEN REST_OF_NAME
ELSE SUBSTRING(REST_OF_NAME, 1, CHARINDEX(' ', REST_OF_NAME) - 1)
END AS FirstName,
CASE
WHEN 0 = CHARINDEX(' ', REST_OF_NAME) THEN NULL
ELSE SUBSTRING(REST_OF_NAME, CHARINDEX(' ', REST_OF_NAME) + 1, LEN(REST_OF_NAME))
END AS REST_OF_NAME,
FullName
FROM (
SELECT
memberid,
CASE
WHEN SUBSTRING(TEST_DATA.Name, 1, 3) IN ('MR ', 'MS ', 'DR ', 'MRS') THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.Name, 1, 3)))
ELSE NULL
END AS TITLE,
CASE
WHEN SUBSTRING(TEST_DATA.Name, 1, 3) IN ('MR ', 'MS ', 'DR ', 'MRS') THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.Name, 4, LEN(TEST_DATA.Name))))
ELSE LTRIM(RTRIM(TEST_DATA.Name))
END AS REST_OF_NAME,
TEST_DATA.FullName
FROM (
SELECT REPLACE(REPLACE(LTRIM(RTRIM(FirstName)), ' ', ' '), ' ', ' ') AS Name, FirstName AS FullName, memberid
FROM (
SELECT FirstName, memberid
FROM tblFamilyMembersDetails_VD
) RAW_DATA
) TEST_DATA
) TITLE
) FullName) X
--UPDATE A SET A.Lastname=B.LastName From tblFamilyMembersDetails_VD A INNER JOIN #Javed B ON A.Memberid=B.MEMBERID
UPDATE A SET A.FirstName=B.FirstName From tblFamilyMembersDetails_VD A INNER JOIN #Javed B ON A.Memberid=B.MEMBERID
DROP TABLE #Javed
No comments :
Post a Comment