How to convert a numeric values to text and replace one numeric value to specific word-CodePudding

In an SQL query, I have a column, in which I want to convert numeric value type to text, that way I can replace only one numeric value which is -1 for the word Unknown, and leave the rest of the other values as it is.

How can I achieve this?

Thanks.

CodePudding user response：

select  col1 
       ,case when col1 = -1 then 'unknown' else cast(col1 as varchar(20)) end as new_col1
from    t

col1	new_col1
1	1
1	1
1	1
-1	unknown
1	1
1	1
1	1
-1	unknown
1	1
-1	unknown

Fiddle

CodePudding user response：

We can put IsNull and NullIf to good use here:

with mytable as (
  select 1 col union select 2 union select -1
)

select IsNull(NullIf(Convert(varchar(10), col), '-1'), 'Unknown') NewValue
from mytable;