Address Clean up using PATINDEX function in sql server

  

Problem: If policy name has policy number, sometime address , below process to extract the policy number from policy Name when policy number column has null, Also Policy has start wtih three digits and four digits. 

Solution: We are applying PATINDEX to check four digits and three digit logic to clean up data and extract the right policy number and Provider name from RAW data. 

Follow below example to understand this case.

CREATE TABLE [abcdefg]

 (

 PolicyNumber VARCHAR(2000),

 policyName VARCHAR(2000)

 )

-- delete from abcdefg

 INSERT abcdefg VALUES

(NULL, 'Three digits policy LLC    321-12-1'), -- Three digits policy in policy name

('567-12-1', 'Three digits policy LLC    321-12-1'), -- three digits policy in policy number

(NULL, '123 apple LLC 1234-12-1') ,  -- policy name has three digits address and four digits policy no

('4567-09-10', 'apple in 1234-12-1') , --both has data

(NULL, 'Tavera 12 LLC     1234-12-1') ,  -- two digits number with four digits

('9876-09-10', 'Hamspa LLC     1234-12-1'),  -- four digts with both places

('1-Mar-23','1234-12-1'),

( '1234-12-1','123 newCorp LLC')

SELECT REPLACE( 

CASE 

WHEN PolicyNumber IS NULL 

THEN 

CASE /* CHECK 1 --> If policyNumber is null and HERE WE ARE CHECKIG FOUR DIGITS and fetching four digits */

  WHEN PATINDEX('%[0-9][0-9][0-9][0-9]%', PolicyName) > 0 

  THEN 'SPC-'+ SUBSTRING(policyName, PATINDEX('%[0-9][0-9][0-9][0-9]%', PolicyName),10) 

ELSE /* CHECK 2 --> If policyNumber is null and HERE WE ARE fetching with three DIGITS */

'SPC-'+ SUBSTRING(policyName, PATINDEX('%[0-9][0-9][0-9]%', PolicyName),10) 

END

WHEN PolicyNumber IS NOT NULL AND PATINDEX('%[0-9][0-9][0-9]%', PolicyNumber) = 0 

THEN 'SPC-'+ SUBSTRING(policyName, PATINDEX('%[0-9][0-9][0-9]%', policyName),10) 

Else 

/* CHECK 3 --> If policyNumber has three digits policy */

'SPC-'+ SUBSTRING(PolicyNumber, PATINDEX('%[0-9][0-9][0-9]%', PolicyNumber),10) 

END,' ','') as CleanupPolicyNumber,

REPLACE (

CASE 

WHEN PATINDEX('%[a-z][A-Z]%', PolicyName)=1 and PATINDEX('%[0-9][0-9][0-9]%', PolicyName) > PATINDEX('%[a-z][A-Z]%', PolicyName)

THEN RTRIM(LTRIM(SUBSTRING(PolicyName, 1, PATINDEX('%[0-9][0-9][0-9]%', PolicyName)-1)))

WHEN PATINDEX('%[0-9][0-9][0-9][0-9]%', PolicyName)> 0 

THEN RTRIM(LTRIM(SUBSTRING(PolicyName, 1, PATINDEX('%[0-9][0-9][0-9][0-9]%', PolicyName)-1)))

ELSE 

PolicyName END 

,'SPC-','')AS ParticipantName, *

FROM abcdefg