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

Question

I have a table with a column that stores random strings like this:

example_id = qwhs77gt65g7*

Some data on the column now has an asterisk (*), while other data does not.

I need to select those that are. I'm using this query:

SELECT example_id FROM example_tbl WHERE example_id LIKE ‘%*%’

Now this wouldn't normally be a problem, but I'm querying millions of rows and from what I can tell, the LIKE operator is affecting my performance. Query will take several hours to complete

My question is what is the alternative to the LIKE operator?

PS The asterisk is always at the end of the string. Not sure if that helps

Answer 1

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:

1. Integer expression, giving the first corresponding position The substring search begins with the character in the string.
2. String: The string is your text.
3. 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.