Concatenation, rounding, and dealing with nulls

I have the following table:

x        y
1        1
1        0
1     null
0        1
0        0
0     null
null     1
null     0
null  null

And I want to produce the following column:

z
11
10
1null
01
00
0null
null1
null0
nullnull

But I’m having difficulty dealing with the rounding that occurs when using coalesce to convert potential null values to 'null'. Trying this:

select concat(cast(coalesce(x, 'null') as int), cast(coalesce(y, 'null') as int))

Gives me the error Numeric value 'null' is not recognized. Even a simple select concat(cast(x as char), cast(y as char)) doesn’t produce the desired results, although no error occurs.

Technically, the following works (using REGEXP_REPLACE). But this seems a bit hacky. Is there a better documented way of doing this?

select
  regexp_replace(concat(case when x is null then 'null' else x end, case when y is null then 'null' else y end), '.[0]+', '')

Answer

Your value are strings, not ints. So, convert them to the right type:

select concat(coalesce(cast(x as varchar(255)), 'null'),
              coalesce(cast(y as varchar(255)), 'null')
             )

Note: Not all databases support varchar(255) in this context and need something like string or char.

Advertisement

Answer