Concatenation not working in SQL Server

Question

I am using SQL Server 2008 R2 and hence I can't use the CONCAT function. I want to concatenate three columns which have numbers in them with the datatype nvarchar.

I tried below two methods and they are not working for me:

select top 10 
    shoppingcartno + lineitemNo + POnumber 
from 
    tbSupplierLineItem

select top 10 
    CONVERT(varchar(30), ShoppingCartNo) +
    CONVERT(varchar(30), lineitemNo) +
    CONVERT(varchar(30), POnumber) 
from 
    tbSupplierLineItem

This is the sample data:

ShoppingCartNo  LineItemNo  DunsNo
--------------------------------------
1000105517      1           009122532
1000427144      2           099441680
1000427144      3           099441680
1000427144      4           099441680
1000491452      3           014296052
1000495759      3           825067460

What does "not working" mean? The second should work (although you should use nvarchar() rather than varchar() based on the types of the strings. — Gordon Linoff
– Gordon Linoff, Commented Sep 8, 2017 at 11:45
POnumber is not in your sample data. Is it supposed to be DunsNo ? — SqlZim
– SqlZim, Commented Sep 8, 2017 at 11:46

Dotnetpickles · Accepted Answer · 2017-09-08 12:15:39Z

1

Try altering the query like below

select top 10 
CONVERT(varchar(30),isnull(ShoppingCartNo,''))
+CONVERT(varchar(30),isnull(lineitemNo,''))
+CONVERT(varchar(30),isnull(POnumber,'')) 
from tbSupplierLineItem

answered Sep 8, 2017 at 12:15

Dotnetpickles

1,0248 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Concatenation not working in SQL Server

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related