Search code examples
sql-servercoalesce

join two columns to one column if they are numeric in SQL 2008

This is what I am using. The problem is with it being numeric. I also need a - to separate.

COALESCE([STORE_RECEIPT_NUM], '') + '-' + COALESCE(e.[STORE_ID], '')

Example:

STORE_RECEIPT_NUM = 12345
STORE_ID = 611

The column RECEIPT_NUM should read 12345-611

However, it adds them and comes out 12956

If I use columns that are not numeric it works great

Solution

  • You don't say what type of SQL you are using but try:

    CAST(COALESCE([STORE_RECEIPT_NUM], '') AS VARCHAR(9)) 
+ '-' 
+ CAST(COALESCE(e.[STORE_ID], '') AS VARCHAR(9))