CODEWITHSUNDEEP
sqloracle-databaseconcatenation
sql-n00b
sql-n00b

Reputation: 91

Only want a Concatenation to appear when the value is not null

I have built a concatenation using SQL (Oracle), but I only want the concatenation to output when the value in the field is not null. I'm effectively building a website URL in the field, but in some cases the link is not yet available, but the concatenation still outputs the prefix (http://www.). If the value is null, then it should output null. At the moment I have:

SELECT 'http://www.'||LINK AS "URL"
FROM TABLE

Upvotes: 1

Views: 49

Answers (3)

David Faber
David Faber

Reputation: 12486

I would go even further. You have Oracle so you have regular expressions at your disposal (or you do if you have 9i or greater), so you can check to see if your link already starts with http://:

SELECT CASE WHEN REGEXP_LIKE(link, '^https?:\/\/') THEN link
  WHEN link IS NOT NULL THEN 'http://www.' || link END AS url
  FROM mytable;

The CASE statement will return NULL if there is no ELSE clause, so you need not add an explicit case for link IS NULL. Personally, I would go so far as to make sure that link didn't start with www. as well, or if it even should.

Upvotes: 0

ruudvan
ruudvan

Reputation: 1371

You could use NVL2 as the other answer suggested. Or alternatively do something like - 

SELECT CASE WHEN LINK IS NOT NULL THEN 
         'http://www.' || LINK 
       ELSE 
         NULL 
       END
          AS "URL"
FROM TABLE;

Upvotes: 0

doelleri
doelleri

Reputation: 19682

If selecting only rows from TABLE where LINK IS NOT NULL isn't an option, you can use NVL2() for this. It accepts three arguments - a string, the value to return if the string is not null, and the value to return if the string is null.

SELECT NVL2(LINK, 'http://www.'||LINK, NULL) AS "URL" FROM TABLE;

Upvotes: 3

Related Questions