SQL – Concatenate

Background

Across the various database systems, data Concatenation used to be everyman for himself.

As in

Oracle uses “|” as in:-


p2.pkey || '-' || i2.issuenum

 

Transact SQL uses “+” as in:-


p2.pkey + '-' + i2.issuenum

DBA

Pity the DBA he gets a query and he has to make changes to fit his\her platform.

If it is a new code, he will appreciate it if you consider using the CONCAT operator.

 

Code Sample

Code Sample -01

SQL


declare @tblCustomer TABLE
(
      id        smallint not null
                  identity(1,1)
    
    , firstname varchar(30) null
    
    , lastname  varchar(60) null
)

insert into @tblCustomer
(firstname, lastname)
values
('Tom', 'Peter')


select 
          id
        , tblC.firstname
        , tblC.lastname
        , tblC.id + tblC.firstname + ' ' + tblC.lastname as 'fullnamePlus'

        
from   @tblCustomer tblC


Output

Output – Image

Output – Text

Conversion failed when converting the varchar value 'Tom' to data type smallint.

Or if we have a date (dateAdded), one will get

Operand type clash: date is incompatible with smallint

 

Code Sample – Use Convert/Cast Operators

SQL


declare @tblCustomer TABLE
(
      id        smallint not null
                  identity(1,1)
    
    , firstname varchar(30) null
    
    , lastname  varchar(60) null
    
    , dateAdded date        null
)

insert into @tblCustomer
(firstname, lastname, dateAdded)
values
('Tom', 'Peter', '2010-07-01')


select 
          id
        , tblC.firstname
        , tblC.lastname
        , cast(tblC.id as varchar(10)) + ' ' + tblC.firstname + ' ' + tblC.lastname + ' ( ' + cast(tblC.dateAdded as varchar(30)) + ' )' as 'fullnamePlus'
        
from   @tblCustomer tblC


Output

Output – Image

 

Explanation

Here is the concatenating operator, when we use the + sign


cast(tblC.id as varchar(10)) + ' ' + tblC.firstname + ' ' + tblC.lastname + ' ( ' + cast(tblC.dateAdded as varchar(30)) + ' )'

 

Code Sample – Use Convert/Cast Operators

SQL


declare @tblCustomer TABLE
(
      id        smallint not null
                identity(1,1)
    
    , firstname varchar(30) null
    
    , lastname  varchar(60) null
    
    , dateAdded date        null
)

insert into @tblCustomer
(firstname, lastname, dateAdded)
values
('Tom', 'Peter', '2010-07-01')


select 
          id
        , tblC.firstname
        , tblC.lastname
        , convert(varchar(30), tblC.dateAdded, 101) as dateAdded
        , concat(tblC.id, ' ', tblC.firstname, ' ', tblC.lastname, ' ', tblC.dateAdded) as 'fullnameConcat'
        
from   @tblCustomer tblC

Output

Output – Image

Explanation

Here is the concat operator:-


concat(tblC.id, ' ', tblC.firstname, ' ', tblC.lastname, ' ', tblC.dateAdded)

Summary

Agreeably it does not mean or save much.

Saving grace remains the fact that CONCAT is cross-platform; whereas its old school sign counterparts ( || and + ) are not.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s