SQLite matching on partial integer

Question

I have the following code:

sic_code = int(input("Filter by SIC code (1-4 digits)? Default is all: ")

# Connect to the database file
conn = sqlite3.connect(db)
c = conn.cursor()

sql = ("SELECT Name "
       "FROM stock_data "
       "WHERE SIC_Code = {sc}".format(sc=sic_code))

# Run SQL query
c.execute(sql)

And here is how the SIC codes look in the database.

Ideally I'd like to match 7372 regardless of whether the user enters 7, 73, 737 or 7372. I've tried starting with a simple query matching on a 4 digit code but I can not get this to work. The query returns no rows. Without the SIC query, everything works.

Both the SIC column and the user input variable is specified as integer.

Thanks in advance for any assistance.

Answer 1

You are looking for LIKE in sqlite.

You use % and _ with LIKE operator

You use % when you want to match some part of string eg.

h% will match with hot , hell...

h_t will match with hut,hit..

The LIKE works with VARCHAR not with integer so w.rt your question if you want to use LIKE to match integer values you have to store them as VARCHAR and then use

#input sic_code should be string
sic_code = input("Filter by SIC code (1-4 digits)? Default is all: ")
sic_code = sic_code + '%' #where SIC_code is stored as `VARCHAR`
SELECT Name FROM stock_data WHERE SIC_Code LIKE {sc}".format(sc=sic_code)

OR

You can use CAST

#input sic_code should be string
sic_code = input("Filter by SIC code (1-4 digits)? Default is all: ")
sic_code = sic_code + '%' #where SIC_code is stored as `integer`
SELECT Name FROM stock_data WHERE CAST(SIC_Code as TEXT) LIKE 
                                   {sc}".format(sc=sic_code)

*I haven't used cast but read the documentation.

Answer 2

Use regexp_match() from SQLAlchemy 1.4:

It works on Integer type columns as well.