CODEWITHSUNDEEP
sqlsql-servercoalesce
Bserk
Bserk

Reputation: 91

Trouble using COALESCE function in sql

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

Upvotes: 0

Views: 1088

Answers (4)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131324

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

Upvotes: 0

Oleg Dok
Oleg Dok

Reputation: 21766

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

Upvotes: 2

sll
sll

Reputation: 62504

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), '')

Upvotes: 1

Blorgbeard
Blorgbeard

Reputation: 103467

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

Upvotes: 6

Related Questions