Trouble using COALESCE function in sql

Question

I am attempting to do the following:

SELECT forename, 
       forename2, 
       surname,
       (SELECT (COALESCE(LEFT(forename, 2)),"") + 
               (COALESCE(LEFT(forename2, 1)),"") + 
               (COALESCE(LEFT(surname, 1)), "") as Mnemonic)
from Persons

trying to get, first 2 letters of forename, first letter of forename2(if NOT null), and first letter of surname if not null, with "" empty strings where a null is present.

any help would be much appreciated, Regards, Bserk

Answer 1

Your question doesn't say what's wrong with your current code, but I'm going to guess you are getting an error because you're using double-quotes instead of single quotes, and your brackets don't match.

You can also simplify this query somewhat by removing the inner select.

Try this:

SELECT forename, forename2, surname, 
    COALESCE(LEFT(forename,  2),'') + 
    COALESCE(LEFT(forename2, 1),'') + 
    COALESCE(LEFT(surname,   1),'') as Mnemonic
from Persons

Answer 2

Use single quotes instead of double for string literals and count your braces:

SELECT 
  forename, 
  forename2, 
  surname,
  COALESCE(LEFT(forename, 2),'') + COALESCE(LEFT(forename2, 1),'') + COALESCE(LEFT(surname, 1), '') as Mnemonic
from Persons

Answer 3

Looks like "trouble" you've mentioned is because using of wrong parenthesis and double quotes.

Following query returns 1123

SELECT   COALESCE(LEFT(null, 2), '')  
         + COALESCE(LEFT('1111', 2), '') 
         + COALESCE(LEFT('2222', 1),'') 
         + COALESCE(LEFT('3333', 1), '')

Answer 4

The statement will not even compile as it is.

• You don't need the second SELECT statement as you are not doing a subquery.
• You need to use single braces. Double braces are used for object names (ie tables, procedures etc).
• Too many parentheses

Try:

SELECT forename, forename2, surname,
    COALESCE(LEFT(forename, 2),'') +
    COALESCE(LEFT(forename2, 1),'') + 
    COALESCE(LEFT(surname, 1), '') as Mnemonic
from Persons