SQL select where column begins with Letters

Question

I have a table tbl1 with row of data:

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

I want to query the the Legal_Doc_No of each ID. The value of each ID is either the TIN or the SSS column.

How can I query the TIN and SSS column starting with letters (none) so that only values starting with numbers will be assigned to 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;

Depending on the database, there might be other solutions.

In SQL Server, you can do:

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 collation, then you'll need to take that into account:

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