0

I have a query that pulls different data but for one field it concats it, since the URL is partially filled in my database:

select blah, blah2, blah3, concat('https://my_url.com', g.logourl ) as logourl

but the problem is if logourl is blank then I just get https://my_url.com instead of the full URL. This is having down stream impact.

Is there a way to only concat if logurl is not null?

Improve this question

3 Answers 3

Reset to default
2

You can try using case when expression

select blah, blah2, blah3, case when g.logourl is not null and g.logourl<>'' then concat('https://my_url.com', g.logourl ) end as logourl
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

oddly this does not work. I get the results in the same way I got it above.
@Lostsoul, I've just added a condition which should solve your problem, I hope
Thank you Fahmi, it's still not working with the update. It's so strange. The column appears empty and I cannot see any special string in it
it worked when I removed your or statement so it's this - case when g.logourl<>'' then concat('my_url.com', g.logourl ) end as logourl
That "or" should be an "and": as currently written, an empty string passes the "is not null" test and the other test doesn't matter, because only one side of the "or" needs to be true.
|
2

You can use:

(case when g.logourl is not null then 'https://my_url.com' || g.logourl end) as logourl

If you want to avoid an empty string (and NULL) you can use:

(case when g.logourl <> '' then 'https://my_url.com' || g.logourl end) as logourl

If you want to eliminate a string of spaces:

(case when replace(g.logourl, ' ', '') <> '' then 'https://my_url.com' || g.logourl end) as logourl
Improve this answer

4 Comments

oddly this does not work. I get the results in the same way I got it above.
@Lostsoul, could you please add some sample data of your table in the question?
@Fahmi I realized the problem, the data isn't null for this column oddly. it's just empty. In DBeaver I see some columns are [Null] but the empty columns for this specific column is just empty.
@Lostsoul, I've just added a condition which should solve your problem, I hope
0

If you want the output to be null if logurl is null, then you can use the concat operator || instead of the concat() function

select 'a' || null; ?column? ---------- (1 row)
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.