Replace a value with another value if that first value is not null

Question

I have the following situation (please run the dummy dataset), whereby FS_Number has true FS_Numbers, NULL and random text, such as N/A_ADMINIS2 This need to be replaced with values from FS_Number1

SELECT 1 AS ID, 214565 AS FS_Number1, Null AS  FS_Number
UNION
SELECT 2 AS ID, null   AS FS_Number1, '759843' AS  FS_Number
UNION
SELECT 3 AS  ID, null   AS FS_Number1, '243545' AS  FS_Number
UNION
SELECT 4 AS ID, null   AS FS_Number1, '655342' AS  FS_Number
UNION
SELECT 5 AS ID, 897324 AS FS_Number1, 'N/A_ADMINIS2' AS  FS_Number
UNION
SELECT 6 AS  ID, 934895 AS FS_Number1, 'N/A_ADMINIS2' AS  FS_Number
UNION
SELECT 7 AS ID, 3453 AS FS_Number1, Null AS  FS_Number
UNION
SELECT 8 AS ID, 85634542 AS FS_Number1, 'N/A_3' AS  FS_Number
UNION
SELECT 9 AS  ID, 3451124 AS FS_Number1, 'N/A_1' AS  FS_Number

looks like this:

ID  FS_Number1  FS_Number
1   214565      NULL
2   NULL        759843
3   NULL        243545
4   NULL        655342
5   897324      N/A_ADMINIS2
6   934895      N/A_ADMINIS2
7   3453        NULL
8   85634542    N/A_3
9   3451124     N/A_1

The result should look like this:

SELECT 1 AS ID, 214565 AS FS_Number1, '214565' AS  FS_Number
UNION
SELECT 2 AS ID, null   AS FS_Number1, '759843' AS  FS_Number
UNION
SELECT 3 AS  ID, null   AS FS_Number1, '243545' AS  FS_Number
UNION
SELECT 4 AS ID, null   AS FS_Number1, '655342' AS  FS_Number
UNION
SELECT 5 AS ID, 897324 AS FS_Number1, '897324' AS  FS_Number
UNION
SELECT 6 AS  ID, 934895 AS FS_Number1, '934895' AS  FS_Number
UNION
SELECT 7 AS ID, 3453 AS FS_Number1, '3453' AS  FS_Number
UNION
SELECT 8 AS ID, 85634542 AS FS_Number1, '85634542' AS  FS_Number
UNION
SELECT 9 AS  ID, 3451124 AS FS_Number1, '3451124' AS  FS_Number

looks like this:

ID  FS_Number1  FS_Number
1   214565      214565
2   NULL        759843
3   NULL        243545
4   NULL        655342
5   897324      897324
6   934895      934895
7   3453        3453
8   85634542    85634542
9   3451124     3451124

I know you can replace NULL values with COALESCE, but in my case I also have text that needs ot be replace with a number. What is the best way to do this?

GMB · Accepted Answer · 2020-09-14 22:42:12Z

You could do this with try_convert():

select id, fs_number1,
    case when try_convert(int, fs_number) is null
        then fs_number1
        else fs_number
    end as fs_number
from mytable

The idea is to try to convert the value to a number. If the conversion attempt returns null, it means that the string is either not numeric, or null: in that case we use the alternative value.

Demo on DB Fiddle:

id | fs_number1 | fs_number
-: | ---------: | --------:
 1 |     214565 |    214565
 2 |       null |    759843
 3 |       null |    243545
 4 |       null |    655342
 5 |     897324 |    897324
 6 |     934895 |    934895
 7 |       3453 |      3453
 8 |   85634542 |  85634542
 9 |    3451124 |   3451124

Moe Sisko · Accepted Answer · 2020-09-15 05:29:13Z

0

This is similar to GMB's version, but uses coalesce instead of case.

select id, fs_number1,
    coalesce(try_convert(int, fs_number), fs_number1) as fs_number
from mytable

answered Sep 15, 2020 at 5:29

Moe Sisko

12.2k8 gold badges52 silver badges88 bronze badges

Collectives™ on Stack Overflow

Replace a value with another value if that first value is not null

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related