Prevent SQLite query from stripping leading zeros from numeric strings?

Question

In my database, a table contains two columns each containing an 8 digit ASCII code, usually it's just alphanumeric. For example, a row might contain A123B45C in col1 and PQ2R4680 in col2.

I need to have a query/view that outputs a 4 character string calculated as the 2nd+3rd chars of these, concatenated. So in this example the extra column value would be 12Q2.

This is a cut-down version of the SQL I'd like to use, although it won't work as written because of zero stripping / conversion:

select

*, 
(substr(col1, 2, 2) || substr(col2, 2, 2)) AS mode

from (nested SQL source query) 

where (conditions)

This fails because if a row contains A00B23B4 in col1 and P32R4680 in col2, it will evaluate as 0032 and the query output will contain numeric 32 not 0032. (It's worse if col1 contains P1-2345 or "1.23456" or something like that)

Other questions on preventing zero stripping and string to integer conversion in Sqlite, all relate to data in tables where you can define a column text affinity, or static (quotable) data. In this case I can't do these things. I also can only create queries, not tables, so I can't write to a temp table.

What is the best way to ensure I get a 4 character output in all cases?

Answer 1

I believe you issue is not with substr stripping characters as this works as expected e.g. :-

Then running query SELECT substr(col1,2,2) || substr(col2,2,2) as mode FROM stripping

results in (as expected):-

Rather, your issue is likely how you subsequently utilise mode in which case you may need to use a CAST expression CAST expressions

For example the following does what is possibly happening :-

`SELECT  substr(col1,2,2) || substr(col2,2,2) as mode, CAST(substr(col1,2,2) || substr(col2,2,2) AS INTEGER) AS oops  FROM stripping`

resulting in :-