SQL String Concatenation

Question

I have a table with columns A,B,C.My requirement is to concatenate the values of columns A and B, and save it into column C. Note: All columns are of Varchar datatype.

For e.g:

If A = 100 and B = 200, C should be 100200
If A = 0 and B = 200, C should be 0200
If A = NULL AND B = NULL, C should be NULL
If A = NULL and B = 01, C should be  01
If A = 01 and B = NULL, C should be 01

Any ideas how this can be achieved using SQL?If only one of the column values is NULL, result should not be NULL.

What I have so far is:

select A+B C from myTable;

Answer 1

-- return non NULL value when concatenating NULL and non-NULL values
SET CONCAT_NULL_YIELDS_NULL OFF

-- prepare sample data
CREATE TABLE #t (
    A varchar(15),
    B varchar(15),
    C varchar(15)
)

INSERT INTO #t (A, B) VALUES
('100', '200'),
('0', '200'),
(NULL, '200'),
(NULL, NULL),
(NULL, '01'),
('01', NULL)

-- concatenate data
UPDATE #t SET
    C = A + B

-- show
SELECT * FROM #t

-- clean up
DROP TABLE #t

Answer 2

Maybe this will help: Some test data:

DECLARE @tbl TABLE(A VARCHAR(10),B VARCHAR(10))

INSERT INTO @tbl
SELECT '100','200' UNION ALL
SELECT '0','200' UNION ALL
SELECT NULL, NULL UNION ALL
SELECT NULL,'01' UNION ALL
SELECT '01',NULL

The query:

SELECT
    tbl.A,
    tbl.B,
    (
        CASE 
            WHEN tbl.A IS NULL AND tbl.B IS NULL
            THEN NULL
            ELSE ISNULL(tbl.A,'')+ISNULL(tbl.B,'')
        END
    ) AS C
FROM
    @tbl AS tbl

Answer 3

declare @T table(A varchar(10), B varchar(10), C varchar(10))

insert into @T(A, B) values
('100' , '200'),
('0'   , '200'),
( null ,  null),
( null , '01'),
('01'  ,  null)

update @T
set C = case when A is not null or B is not null 
             then isnull(A,'')+isnull(B,'') 
        end