Sharktooth Sharktooth - 5 months ago 5
SQL Question

Concatenate a single row with multiple string validations?

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

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, '')