What is an alternative to the LIKE operator when querying a large number of rows?

Question

I have a table that has a column that stores random strings like this: example_id=qwhs77gt65g7* Now some of the data on that column has an asterisk (*) while other data doesn't. I need to choose those that have. I'm using this query: SELECTexample_idFROMexample_tblWHEREexample_idLIKE '%*%' Now this normally wouldn't be a problem, but I'm querying millions of rows and from what I can tell the LIKE operator is affecting my performance. How many are needed to complete the query?

P粉063862561 · Answer

Since you mentioned "the asterisk is always at the end" then you can try

Where example_id LIKE '%*'.

This will find any value ending with "*"

or

is to search for substrings in the columns of the table.

One way to achieve it is to use the instr() function, which takes 3 parameters.

Syntax: instr(rank, string, sub_string)

Ranking:

Integer expression, giving the first corresponding position The substring search begins with the character in the string.
String: The string is your text.
sub_string: The substring you are looking for Looking for. If instr() is not found, returns 0 match.

Now how do I apply this to the table? Since the instr() function is x3, it is simple to apply.

For example: filter[ZCT] where instr(1,ALLOTEDTO,”Ram”) 0.

Where 1 is the starting position to find the substring, ALLOTEDTO is the column name composed of strings, and the last parameter is the substring itself. This will give you all records in the table where the ALLOTEDTO column contains the substring "Ram"

This is equivalent to.

Select * from ZCT where ALLOTEDTO is similar to "%Ram%".

Note: The Instr() function is case-sensitive, so always use uppercase or lowercase functions.