sqlsql-server

Check column has 'letter' or '-' then bring value from another column


I have table 1 data and I need to create another column as on criteria

If id has "-" or "any letters" bring value from invoice

Table1

+---------+---------+
|   id    | invoice |
+---------+---------+
| 1234    |    2534 |
| 9870    |    6542 |
| ABC234  |    9874 |
| 34-5469 |  325416 |
+---------+---------+

Expected Result as id2

+---------+---------+--------+
|   id    | invoice |  id2   |
+---------+---------+--------+
| 1234    |    2534 |   1234 |
| 9870    |    6542 |   9870 |
| ABC234  |    9874 |   9874 |
| 34-5469 |  325416 | 325416 |
+---------+---------+--------+

Solution

  • You can use the isnumeric function to find out whether the id is an int or not.

    select *,
    case when isnumeric(id) = 1 then id else invoice end as id2
    from [yourtable]
    

    isnumeric does not provide credible results all the time and hence if you are using SQL Server 2012 or 2014 and above you may go for try_cast instead.

    select * 
    ,case when try_cast(id as int) is not null then id else invoice end as id2
    from [yourtable]