0

So lets say I have this list of strings in an Excel file:

33000
33100
33010
33110
45050
45150
45250
45350
45360
45370
55360
55370

And I've got a SQL table that has this list of strings and more and I want to make a SELECT statement that searches only for this list of strings.

I could make a brute force statement like SELECT * FROM Table WHERE field = '33100' OR field = '33010' .... However I could make the WHERE list smaller by using LIKE statements.

I'm trying to find a way to make the number of LIKE statements as small as possible so I need to generate the least amount of SQL patterns to identify the whole list. For the list above, the least amount of SQL patterns would be this:

33[01][01]0
45[0123]50
[45]53[67]0

How could I generate a list of patterns like this dynamically where the input is the list of strings?

LCIII
  • 3,102
  • 3
  • 26
  • 43
  • 1
    This seems like a similar question: https://stackoverflow.com/questions/10349203/wildcard-of-number-in-sql-server Have you tried: `SELECT * FROM Table WHERE str LIKE '[0-9][0-9][0-9][0-9][0-9]'` ? (not sure if there is a way to indicate an exact number of matches like by following the pattern with `(5,)`) – Mixolydian Feb 04 '19 at 21:18
  • This doesn't make sense to me. If you want all the rows just don't use a where clause. I suspect that isn't what you really mean though. – Sean Lange Feb 04 '19 at 21:19
  • @Mixolydian I'm going to guess that the list is meant to include only some specific rows from the table, not all numeric strings in the table, and he's trying to find the most efficient way to use `LIKE` for this smaller list. That said, I don't understand the choice to do that over a plain join to the list. – ZLK Feb 04 '19 at 21:20
  • You might also prefer `REGEXP` to `LIKE` depending on what you're trying to do - see also https://stackoverflow.com/questions/20794860/regex-in-sql-to-detect-one-or-more-digit – Mixolydian Feb 04 '19 at 21:20
  • @ZLK that makes sense - and I like your idea of joining to the smaller list. – Mixolydian Feb 04 '19 at 21:21
  • @ZLK . . . The most efficient way to use `LIKE` is probably to use `IN` instead. – Gordon Linoff Feb 04 '19 at 21:25
  • Your example is incorrect. Your proposed rewrite would allow `33000` but that isn't in the list – Martin Smith Feb 04 '19 at 22:32
  • @MartinSmith Ah you're right. I've corrected the list. – LCIII Feb 05 '19 at 13:41

2 Answers2

1

An alternative approach might be more "elegant", but it will not be faster. Your strings start with different characters, so the first part of a like pattern would be a wildcard or character range -- effectively precluding the use of an index.

A simple in expression, on the other hand, can use an index:

where col in ('33100', '33010', '33110', '45050', ...)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I've updated the question to be a bit more specific. I think I wasn't clear originally. – LCIII Feb 05 '19 at 14:30
  • @LCIII . . . Nothing in your edited question changes this answer. – Gordon Linoff Feb 05 '19 at 14:57
  • Sorry, I was hoping to get a list of patterns that identify all strings in a list however you haven't posted any patterns in your answer. You seem to be giving me a simple IN statement for all the items, which is the original brute force method I was trying to avoid. I feel like I'm missing something obvious... – LCIII Feb 05 '19 at 15:29
  • 1
    @LCIII . . . Don't avoid the brute force method. It should be more efficient than `LIKE`. If you want it to be even faster, load the list into a table with one column and make that column the primary key. Then use `JOIN`/`EXISTS`/`IN` for the logic. – Gordon Linoff Feb 05 '19 at 15:53
1

  1. Okay, let's say you have this data in Excel which starts from cell A2

  2. In cell C1 write this code: create table ##TEMP(STRS varchar(20))

  3. In cell C2 write this code: ="insert into ##TEMP"&" values"&" ('"&A2&"' )"&","

  4. In cell C3 write this code: =" ('"&A3&"')"&","

  5. Now Ctrl+C formula in cell C3 and paste it in range C4-C13

enter image description here

  1. Now you get Excel like this

enter image description here

  1. Copy this code in range C1-C13, open SQL management studio paste it, delete last comma (in this case in cell C13 there is comma at the end you have to delete it for success SQL run) and run, now in you have ##temp table.

  2. INNER JOIN it with your table like

    SELECT * FROM MYTABLE M INNER JOIN ##TEMP AS T ON T.STRS = M.COLUMN_NAME_STR

And you should get data which you need, hope it helps.

Vaso Miruashvili
  • 101
  • 1
  • 11
  • I've updated the question to be a bit more specific. I think I wasn't clear originally. – LCIII Feb 05 '19 at 14:30