How to realize the query of multiple values of like by Oracle

Background description of the problem:

One day the customer has a demand, given a batch of mobile phone numbers or phone numbers, query out the relevant call records, as well as some related information.

The dialed number given by the customer is shown in the figure:

The query results are shown in the following figure (the results of this batch are not queried by the results imported in the above figure, so two images with irrelevant results are imported for format description):

Because the dialed number given by the customer is not standard, it is troublesome to inquire.

Analysis process:

I created a new table security_phonebill_callee_num to store the imported dialed number information

All call data is saved in t_phonebill_201702. If you want to query, you must implement like, which is the implementation effect of the following sql

Select org_caller_num,org_callee_num,call_seconds,start_time,switch_id,in_trunk,out_trunk,settle_carrier,file_namefrom t_phonebill_201702 awhere a.org_callee_num like '800100186%'

But there are many such numbers, sometimes more than a hundred, and the above sql can only query the call records of one number.

At first I wanted to implement it with a cursor, write a cursor, put the called number in the cursor, and then write a loop and query it in turn each time.

But later found that the amount of t_phonebill_201702 data is too large, like takes 20 minutes at a time, 100 is 2000 minutes (30 hours), time-consuming is too large, efficiency is too inefficient.

Later, after consulting the data, I tried to write down the sql many times, and finally realized the query, and the efficiency of the experimental demonstration was also good.

Select org_caller_num,a.org_callee_num,call_seconds,start_time,switch_id,in_trunk,out_trunk,settle_carrier,file_namefrom t_phonebill_201702 a where exists (select 1 from security_phonebill_callee_num c where a.org_callee_num like'% | | c.org_callee_num | |%')

If the amount of data in the t_phonebill_201702 table is small, you can consider using the simplified version, which is easier to understand and more clearly understand how multiple values of like are implemented, but using exists is always a good habit. If you have similar needs, I hope I can help you.

Select org_caller_num,a.org_callee_num,call_seconds,start_time,switch_id,in_trunk,out_trunk,settle_carrier,file_namefrom t_phonebill_201702 a securityphonebillage calleeroomnum cwhere a.org_callee_num like'% | c.org_callee_num | |%'

Summary

The above is the whole content of this article, I hope that the content of this article has a certain reference and learning value for your study or work, if you have any questions, you can leave a message and exchange, thank you for your support.