Getting numbers of certain length in text-CodePudding

I have a table like this

CREATE TABLE "demo" (
"1" TEXT,
"2" TEXT);

This table contains data such as:

1   Python 1234567
2   Python/7894561
3   1346792 Python
4   9764312/Python

What I want to do is in the data in the TEXT column number 2, there are 7-character numbers specified with "0000000", I just want to get these numbers

SELECT Substr("2", 1,instr("2"," ") - 0) AS Output
FROM demo

I'm using this query, but I can't get the result I want. Can you help me?

CodePudding user response：

You can use something like this:

a = "Python 0000000"
b = "Python/0000000"
c = "0000000 Python"
d = "0000000/Python"

elements = [a, b, c, d]

for el in elements:
    if a.find("0000000") == -1:
        print("No '0000000' here!")
    else:
        print("Found '0000000' in the string.")

And use regex to get others values within each string.

CodePudding user response：

If these 7-digit numbers are always at the beginning or at the end of the string (like your sample data) then use a CASE expression to get the first or the last 7 chars:

SELECT CASE SUBSTR("2", 1, 7)   0 
         WHEN 0 THEN SUBSTR("2", LENGTH("2") - 6)
         ELSE SUBSTR("2", 1, 7)
       END AS Output 
FROM "demo";

See the demo.