I have a text file contains around 300 lines, each line contains first 3 characters of the postal codes. like this,
M2X
I5D
T8I
O0P
K6R
I am looking for the best way to use this list in my query Where clause to match all the records from a table where first 3 digits is in this list.
How can I ready txt file then create an array then use it in Where
Update my answer:
WHERE POST regexp '^(M2X|I5D|T8I|.....)'
WHERE POST rlike '^(M2X|I5D|T8I|.....)'
You probably want to use the SQL IN ( ) predicate for this.
You'd want it to look like:
WHERE post_code IN ('M2X','I5D','T8I','O0P','K6R',...)
Converting a file of text lines into that format can be done in a variety of scripting languages. For example, sed and awk:
sed -e "s/.*/'&'/" postal_codes.csv | awk 'BEGIN { ORS="," } { print }' | sed "s/,$//"
'M2X','I5D','T8I','O0P','K6R'
If you only want the first three characters of your data to match the postal code, in other words as if they are prefixes, you can try this:
WHERE LEFT(post_code, 3) IN ('M2X','I5D','T8I','O0P','K6R',...)
That has the disadvantage of not being able to search using an index.
Another option is to use pattern-matching, which will use the index if you search on the prefix. But this makes the query rather long:
WHERE post_code LIKE 'M2X%'
OR post_code LIKE 'I5D%'
OR post_code LIKE 'T8I%'
OR post_code LIKE 'O0P%'
OR post_code LIKE 'K6R%'
OR ...
Or you can load the set of 300 post codes (with the % wildcard at the end) into a temp table, and do it with a JOIN:
SELECT ...
FROM mytable JOIN tmp_post_codes
ON post_code LIKE post_code_pattern
M2X R4T, my list is only contains the first 3 digits of the postal code in each line. I am trying to pull all the records from my table that postal code matches the first 3 digits in my list.where postal_code regexp '^[M2X|I5D|T8I|.....]' or where postal_code rlike '^[M2X|I5D|T8I|.....]'