how to query by checking if specific fields start with a value from a given array?-CodePudding

(MySQL) I have a query to check if 'phone_number' or 'fax_number' startsWith a value from a given array,

lets say const possibleValues = [123,432,645,234]

currently my query runs with the 'or' condition, to check if -

'phone_number' or 'fax_number' that starts with 123 
or 
'phone_number' or 'fax_number' that starts with 432
or
'phone_number' or 'fax_number' that starts with 645
or
'phone_number' or 'fax_number' that starts with 234

it runs extremely slow on a big database, and I wish to make it faster, is there a way to make it run faster?

I'm kinda new to sql queries,

any help would be highly appreciated!

CodePudding user response：

Try creating an in-line table and join with it.

WITH telnostart(telnostart) AS (
          SELECT '123'
UNION ALL SELECT '432'
UNION ALL SELECT '645'
UNION ALL SELECT '234'
)
SELECT
  *
FROM your_search_table
JOIN telnostart ON (
   LEFT(tel_number,3) = telnostart
OR LEFT(fax_number,3) = telnostart

CodePudding user response：

you can use a case statement to add a flag column

select *
,case when left(phone_number,3) in (123,432,645,234) or left(fax_number,3) in (123,432,645,234) then 1 else 0 end as contact_check_flag 
from table_name

As per your requirement, you can filter it or use it elsewhere.

CodePudding user response：

You can try something like:

SELECT * FROM table_1 
WHERE CONCAT(',', `phone_number`, ',') REGEXP ',(123|432|645|234),'  
OR CONCAT(',', `fax_number`, ',') REGEXP ',(123|432|645|234),';

Demo