Using LIKE for SQL Server with Python and list of values

Question

I have a df with clients and products, and I have to get all information about this items from SQL Server, but I have to parsed with substring, because DateBase contains product path, like this:

productpath:

fruits/apple
vegetables/tomato

And list of my products looks like this, but real list contains more than 1000 values:

df['prod']= ['apple', 'orange', 'tomato']

So I decided to use this code:

sql_sales = """
SELECT
    date,
    client,
    productpath,
    cost
FROM all_sales
WHERE 
    client == %(c1)s
    AND productpath LIKE '%%(prod)s%'

"""

And I've got mistake with using LIKE with this part '%%(prod)s%' like this:

not enough arguments for format string unable to rollback

Without LIKE-part it is working

Side note client == isn't valid syntax. T-SQL uses = for both assignment and comparison. — Thom A
– Thom A ♦, Commented Jul 14, 2020 at 11:53

Gordon Linoff · Accepted Answer · 2020-07-14 12:59:57Z

1

One method uses a subquery:

. . . 
where . . . and
      exists (select 1
              from string_split('apple,orange,tomato', ',') s
              where all_sales.productpath like concat('%', s.value, '%')
             )

edited Jul 14, 2020 at 12:59

answered Jul 14, 2020 at 11:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Thom A Over a year ago

split_part still isn't a SQL Server function, Gordon. If you're thinking of STRING_SPLIT then you need to provide 2 parameters, the string to split and the delimiter.

Valeria Over a year ago

Unfortunatelly my list with products contains more than 1000 items, that is why I can't put list in brackets

Gordon Linoff Over a year ago

@Larnu . . . Arrgh. To much Postgres thinking. I fixed the answer. Ironically, I would need multiple arguments for that original function in Postgres as well.

Thom A Over a year ago

No worries, Gordon. :)

Collectives™ on Stack Overflow

Using LIKE for SQL Server with Python and list of values

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related