CODEWITHSUNDEEP
sqlsql-serversql-server-2012concatenation
G Wizard
G Wizard

Reputation: 45

How to Concatenate in SQL

In SQL Server 2012, I'm trying to select mutiple columns and concatenate them as below.

LTRIM(RTRIM(ISNULL(S.TITLE, ''))) + 
' ' + 
LTRIM(RTRIM(ISNULL(S.FIRSTNAME, ''))) + 
' ' + LTRIM(RTRIM(ISNULL(S.SURNAME, ''))) AS 'Full Name',

The problem I have is that if the first column 'TITLE' is NULL or Blank then the next column 'FIRSTNAME' starts with a space due to the concatination.

Any ideas?

P.S - This is my first question so apologies if I have done anything wrong....

Upvotes: 0

Views: 224

Answers (4)

t-clausen.dk
t-clausen.dk

Reputation: 44316

This can be solved very easy by using CONCAT which was introduced in sqlserver 2012.

SELECT 
  CONCAT(LTRIM(RTRIM(S.TITLE)) + ' ', 
    LTRIM(RTRIM(S.FIRSTNAME)) + ' ', 
    LTRIM(RTRIM(S.SURNAME))) AS [Full Name]
FROM ..

Upvotes: 0

Kaf
Kaf

Reputation: 33809

Use it like below. I have omitted LTRIM() and RTRIM() for clarity. And using COALESCE() insted ISNULL()

SELECT
   COALESCE( s.Title + ' ', '') + COALESCE( s.Firstname + ' ', '') +
   + COALESCE( s.surname, '')

Use trimming within COALESCE(). For example;

COALESCE( LTRIM(RTRIM( s.Title)) + ' ', '')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You could just solve that problem using ltrim() around the entire expression:

LTRIM(LTRIM(RTRIM(ISNULL(S.TITLE, ''))) + ' ' + LTRIM(RTRIM(ISNULL(S.FIRSTNAME, ''))) + ' ' + LTRIM(RTRIM(ISNULL(S.SURNAME, '')))) AS [Full Name],

Helpful hint: don't use single quotes for column aliases. Although this sometimes works, at other times, the code will not do what you expect. Only use single quotes for string and date constants.

There are other methods, such as adding the space before the test for NULL

(COALESCE(LTRIM(RTRIM(s.Title)) + ' ', '') +
 COALESCE(LTRIM(RTRIM(S.FirstName)) + ' ', '') +
 COALESCE(LTRIM(RTRIM(s.SurName)), '')
)

This has the advantage of handling the double spaces if there is no first name. However, you will need an rtrim() for the entire expression to handle no surname.

Upvotes: 1

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

Enclose whole one within LTRIM( 

LTRIM(LTRIM(RTRIM(ISNULL(S.TITLE, ''))) + 
' ' + 
LTRIM(RTRIM(ISNULL(S.FIRSTNAME, ''))) + 
' ' + LTRIM(RTRIM(ISNULL(S.SURNAME, '')))) AS 'Full Name',

Upvotes: 1

Related Questions