SQL chooses where the column begins with letters

Question

I have a tbl1 table with a data row:

ID       TIN     SSS
10001    none    1000-20
10002    69098   PRC
10003    69099   INC

I want to request the value of Legal_Doc_No for each identifier. The value of each identifier is a TIN or SSS column.

How can I query the TIN and SSS columns starting with the letters (none) so that only values ​​starting with numbers will be assigned Legal_Doc_No

Select
ID,
'Legal_Doc_No' = case when TIN = Letter then SSS
 else TIN end
from tbl1

Answer 1

Most databases support left(), so you can do something like this:

select id,
       (case when left(time, 1) between 'a' and 'z' or left(time, 1) between 'A' and 'Z'
             then SSS else TIN
        end) as Legal_Doc_no
from tbl1;

There may be other solutions depending on the database.

In SQL Server, you can:

select id,
       (case when time like '[a-z]%'
             then SSS else TIN
        end) as Legal_Doc_no
from tbl1;

If you have a case sensitive account, then you need to consider this:

select id,
       (case when lower(time) like '[a-z]%'
             then SSS else TIN
        end) as Legal_Doc_no
from tbl1;

Answer 2

, , TIN SSS, , (.. ):

SELECT ID,
    CASE WHEN TIN LIKE '%[0-9]%' THEN TIN ELSE SSS END AS Legal_Doc_No
FROM tbl1