CODEWITHSUNDEEP
sql
GK89
GK89

Reputation: 664

SQL convert to all caps in a case when

I am writing some sql to standardize country names. I have written the following:

SELECT
CASE Country
WHEN 'USA' THEN 'UNITED STATES'
WHEN 'US' THEN 'UNITED STATES'
WHEN 'CAN' THEN 'CANADA'
WHEN 'CA' THEN 'CANADA'
WHEN 'AU' THEN 'AUSTRALIA'
WHEN 'AUS' THEN 'AUSTRALIA'
ELSE Country
END AS Country_Standard  
FROM TABLE1

I would like to add some logic that would say, if length of string in country is greater than or equal to 4 then capitalize the whole string. Could that be added to a CASE WHEN clause? The below code does not work as an additional clause, but would some variant be possible?

WHEN LEN() >= 4 THEN UPPER()

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You need an argument to UPPER(). So, you should use the other form of the CASE:

SELECT (CASE WHEN Country IN ('USA', 'US') THEN 'UNITED STATES'
             WHEN Country IN ('CAN', 'CA') THEN 'CANADA'
             WHEN Country IN ('AUS', 'AU') THEN 'AUSTRALIA'
             WHEN LEN(Country) > 4 THEN UPPER(Country)
             ELSE Country
        END) AS Country_Standard

Upvotes: 3

Related Questions