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