CODEWITHSUNDEEP
sqlsql-server
shankshera
shankshera

Reputation: 985

SQL SERVER - Concat NULL Numeric Column

I have this table

CREATE TABLE [name].[tblRange] 
(
  [RangeId_PK] numeric(6, 0) IDENTITY(1, 1) NOT NULL,
  [Lower] numeric(18, 2) NOT NULL,
  [Upper] numeric(18, 2) NULL
)

I want to concat Lower and Upper but it always give me NULL if Upper contain NULL value.

This is my query:

SELECT CAST(Lower AS VARCHAR(50)) + '-' + CAST(Upper AS VARCHAR(50))
FROM name.tblRange

I can't use the following query because it will give this error (SQL Server Database Error: Error converting data type varchar to numeric.)

SELECT CAST(Lower AS VARCHAR(50)) + '-' + CAST(ISNULL(Upper, '') AS VARCHAR(50))
FROM name.tblRange

Please note that I can't use ISNULL(Upper, 0) because that is a upper limit (can't be 0).

How do I solve this problem?

Upvotes: 1

Views: 178

Answers (2)

Patrick Hofman
Patrick Hofman

Reputation: 156948

You were far on the right way by using isnull.

You can use something like this to use the correct data types:

This one shows 0, 999 as values when lower and upper are null:

SELECT cast(isnull(Lower, 0) AS VARCHAR(50))
       + '-'
       + cast(isnull(Upper, 999) AS VARCHAR(50))
FROM name.tblRange

Or this one shows only the dash when lower and upper are null:

SELECT isnull(cast(Lower AS VARCHAR(50)), '')
       + '-'
       + isnull(cast(Upper AS VARCHAR(50)), '')
FROM name.tblRange

Upvotes: 3

Pred
Pred

Reputation: 9042

The problem is that the ISNULL gets two different types as arguments. Switch the CAST and ISNULL statements:

SELECT CAST(Lower AS VARCHAR(50)) + '-' + ISNULL(CAST(Upper AS VARCHAR(50)), '') FROM name.tblRange

Upvotes: 5

Related Questions