Concatenate a single row with multiple string validations?

Question

In the query below, if both conditions are true then the last entry is updated in ErrorMessage. If one record has country more than 3 characters and length of HR_DOMAIN_Code is not equal to 3, then ErrorMessage should look like 'Invalid Country' & 'Invalid HR_DOMAIN_CODE':

update A     

set ErrorMessage =       

(      

 case when len(Country) > 3    then 'Invalid Country'        

 when len(HR_DOMAIN_CODE) != 3   then 'Invalid HR_DOMAIN_CODE'        

 else NULL       

 end)

If this possible using the case statement?

Answer 1

You can just add another WHEN to handle both conditions:

UPDATE A
SET ErrorMessage =
    CASE
        WHEN LEN(Country) > 3 AND LEN(HR_DOMAIN_CODE) != 3 THEN 'Invalid Country & Invalid HR_DOMAIN_CODE'
        WHEN LEN(Country) > 3 THEN 'Invalid Country'
        WHEN LEN(HR_DOMAIN_CODE) != 3 THEN 'Invalid HR_DOMAIN_CODE'
        ELSE NULL
    END

However, the above will be very complicated once you have multiple combinations to check. My suggestion is to use a separate CASE expression of each of the condition. Something like this:

UPDATE A
SET Errormessage = STUFF((
    SELECT
        CASE WHEN LEN(Country) > 3 THEN '& Invalid Country' ELSE '' END + 
        CASE WHEN LEN(HR_DOMAIN_CODE) != 3 THEN '& Invalid HR_DOMAIN_CODE' ELSE '' END
    ), 1, 2, '')

Concatenate a single row with multiple string validations?

Sign up