SQL QUERY - check column has 'letter' or '-' then bring value from another colum-CodePudding

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 |
             --------- --------- --------

CodePudding user response：

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

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

Edit: isnumeric is not providing credible results all the time and hence if you are using SQL server 2012 or 2014 and above you may go for try_cast

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

CodePudding user response：

Assuming that you are just looking for values that have a letter or a hyphen (-) you could use a CASE expression and a LIKE like this:

SELECT CASE WHEN id LIKE '%[A-z-]%' THEN invoice ELSE id END
FROM dbo.YourTable;

What would likely be better, however, is to check that id doesn't value any characters apart from digits:

SELECT CASE WHEN id LIKE '%[^0-9]%' THEN invoice ELSE id END
FROM dbo.YourTable;