Looping through a Where statement until a result is found (SQL)

Question

Summary of the problem:

I use Python to send a series of queries to the database (one after the other) from the loop until a nonempty result set is found. The request has three conditions that must be met, and they are placed in the where statement. Each iteration of the cycle changes and controls the conditions from a specific condition to a more general one.

Details:

Assuming conditions are keywords based on a pre-prepared list ordered by accuracy, for example:

Option KEYWORD1   KEYWORD2   KEYWORD3 
  1     exact      exact      exact     # most accurate!
  2     generic    exact      exact     # accurate
  3     generic    generic    exact     # close enough
  4     generic    generic    generic   # close
  5     generic+   generic    generic   # almost there
  .... and so on.                      

On the database side, I have a description column that should contain all three keywords, either in their specific form or in general form. When I run the loop in python, this is what actually happens:

-- The first sql statement will be like

Select * 
From MyTable
Where Description LIKE 'keyword1-exact$' 
  AND Description LIKE 'keyword2-exact%' 
  AND Description LIKE 'keyword3-exact%'

-- if no results, the second sql statement will be like 

Select * 
From MyTable
Where Description LIKE 'keyword1-generic%' 
  AND Description LIKE 'keyword2-exact%' 
  AND Description LIKE 'keyword3-exact%'

-- if no results, the third sql statement will be like 

Select * 
From MyTable
Where Description LIKE 'keyword1-generic%' 
  AND Description LIKE 'keyword2-generic%' 
  AND Description LIKE 'keyword3-exact%'

-- and so on until a non-empty result set is found or all keywords were used

, ( , , )

:

, , , .

Python (, , )?

Answer 1

select top 1
    * 
from
(
    select
        MyTable.*,
        accuracy = case when description like keyword1 + '%'
            and description like keyword2 + '%'
            and description like keyword3 + '%'
        then accuracy
        end
    -- an example of data from MyTable
    from (select description = 'exact') MyTable
    cross join      
    (values 
        -- generate full list like this in python 
        -- or read it from a table if it is in database
        (1, ('exact'), ('exact'), ('exact')),
        (2, ('generic'), ('exact'), ('exact')),
        (3, ('generic'), ('generic'), ('exact'))
    ) t(accuracy, keyword1, keyword2, keyword3)
) t
where accuracy is not null
order by accuracy

Answer 2

. , .

Select * 
From MyTable
Where Description LIKE '%iPhone%'

iPhone. , .. . , .

, OR

Select * 
From MyTable
Where Description LIKE '%iPhone%' OR
      Description LIKE '%i-Phone%'

.

Answer 3

Try using RegEx regular expressions for the Sql server. Or else you can try impoting re in python for regex. You can collect data first, and then try repeating to achieve the goal. Hope this will be helpful.