How to add string to column values but ignore empty fields

Question

I have the following code below to select a column from my table and add 'abcd' string to all the values in my column. However, how would I go about ignoring the empty values so rows 10-14 would be empty and while rows 1-9 would remain the same?

SELECT 

vendor + 'abcd'AS [vendor_key]

FROM [table]

Ullas · Accepted Answer · 2021-02-18 06:15:59Z

1

Use a CASE expression.

Query

select case when len(ltrim(rtrim([vendor]))) > 0 then [vendor] + 'abcd'
            else [vendor] end as [vendor_key]
from [your_table_name];

edited Feb 18, 2021 at 6:15

answered Feb 18, 2021 at 4:50

Ullas

11.6k5 gold badges36 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Xin Over a year ago

Thanks for the response however, I am getting the same values as the original column without the added string.

Gordon Linoff · Accepted Answer · 2021-02-18 13:02:19Z

1

Presumably, vendor is an empty string. Just convert it to NULL in this case:

SELECT NULLIF(vendor, '') + 'abcd' AS vendor_key
FROM [table]

Note that + returns NULL if any of the arguments are NULL. This contrasts with CONCAT() which ignores NULL values.

You don't seem to have any spaces in the results, so you don't seem to have spaces in the original data -- and trim() does not seem necessary.

answered Feb 18, 2021 at 13:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

How to add string to column values but ignore empty fields

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related