Transact SQL :- String Concatenate using the Plus ( + ) Sign

Background

I was doing a bit of code review.

The developer cached data into a temporary table and wanted to review the fetched data.

 

Code

Outline

  1. Temporary Table
    • Temporary Table Create
    • Populate data into the temporary table
  2. Concatenate String
  3. Display concatenated string
  4. Temporary Table
    • Drop Temporary table

SQL


set nocount on;

/*
    Declare variables
*/

declare @CHAR_TAB   char(1);
declare @CHAR_CRLF  char(2);

declare @data varchar(max);
declare @sep  varchar(10);
declare @lNumberofRecordsAffected int;


/*
    Set Constants
*/
set @CHAR_TAB  = char(9);
set @CHAR_CRLF = char(13) + char(10);

/*
    Drop Temporary Table
*/
if object_id('tempdb..#account') is not null
begin

    drop table #account

end

/*
    Create Temporary Table
*/
if object_id('tempdb..#account') is not null
begin

    drop table #account

end

CREATE TABLE #account
(
       id int not null 
              identity(1,1)

    ,  [name] varchar(60) NOT null

    ,  [active] bit not null
                default 1
)
;

/*
    Populate Temporary Table
*/
insert into #account
(
  	[name]
)
values 
  ('Sammy Hager')
, ('Bett Midler')
, ('Ben Johnson')
, ('Matt Damon')
, ('Florence Griffith Joyner')
;

/*
    Specify separator
*/
set @sep = @CHAR_TAB;

/*
    Concatenate String
*/
select @data = isNull
                    (
                        @data
                        , ''
                    )

                    + isNull
                        (
                            [name]
                            , ''
                        )

                    + isNull
                        (
                            @sep
                            , ''
                        )
                
                
from   #account
;

/*
    Get Number of Records affected by last operation
*/
set @lNumberofRecordsAffected = @@ROWCOUNT

/*
    Strip last separator
*/
set @data = substring
            (
                    @data
                ,   1
                ,   len(@data) - len(@sep)
            )
                

/*
    print @lNumberofRecordsAffected
*/

/*
    Display Result set
*/
select 
      [@data] = @data
;

/*
    Drop Temporary Table
*/
if object_id('tempdb..#account') is not null
begin

    drop table #account

end

Output

 

Summary

In Transact SQL, the overloaded plus (+) sign is very useful and versatile,

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