Procura HACC MDS Statistical Linkage Key SQL SELECT

procura-hacc-mds-letters-of-name.sql

-- Government of Western Australia
-- Department of Health
-- HACC – Minimum Data Set
-- http://ww2.health.wa.gov.au/Articles/F_I/HACC-minimum-data-set
-- http://ww2.health.wa.gov.au/~/media/Files/Corporate/general%20documents/HACC/PDF/WA_MDS_User_Guide2014.pdf

SELECT 
		UPPER(CONCAT(
			SUBSTRING(
				LEFT(CONCAT(
					REPLACE(
						REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
						CLIENTS.LASTNAME
						, ' ', ''), '''', ''),'(',''),')',''),'-','')
					,' ','2')
				,'22222'),5)
			,2,2),
			SUBSTRING(
				LEFT(CONCAT(
					REPLACE(
						REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
						CLIENTS.LASTNAME
						, ' ', ''), '''', ''),'(',''),')',''),'-','')
					,' ','2')
				,'22222'),5)
			,5,1),
			SUBSTRING(
				LEFT(CONCAT(
					REPLACE(
						REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
						CLIENTS.FIRSTNAME
						, ' ', ''), '''', ''),'(',''),')',''),'-','')
					,' ','2')
				,'22222'),5)
			,2,2)
		)) AS LettersOfName

FROM CLIENTS