0

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

example_id = qwhs77gt65g7*

Now some of the data on that column has asterisks(*) while others don’t have one.

I need to select those that has one. I’m using this query:

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

Now this is usually not a problem but I’m querying millions of rows and as I understand LIKE operator is affecting my performance. It takes hours to complete the query

My question is whats the alternative to the LIKE operator?

PS the asterisks is always at the end of the string. I dont know if that can help

Rei
  • 313
  • 2
  • 11
  • `SUBSTRING(the_field, -1, 1)` will get the last character... but it won't speed up the query, because still every single row must be examined. – Honk der Hase Nov 08 '22 at 06:53
  • Store structured data in *S*tructured Query Language database. If you stored the reverse of `example_id` it would be easy to find with `LIKE '*%'`. – danblack Nov 08 '22 at 06:55
  • 1
    You can opt for elastic search. – nice_dev Nov 08 '22 at 07:02
  • Can the `*` only be at the end? If you want speed, you must re-think the schema. You essentially have two things concatenated together in a single column; defeats performance. – Rick James Nov 08 '22 at 22:05

1 Answers1

0

Since you're mentioning "The asterisks are always at the end" then you can try

WHERE example_id LIKE '%*'.

This will finds any values that end with "*"

OR

Is to search for sub-string in columns of the table.

The one way to achieve it to use instr() function, instr() function takes 3 parameters in account .

Syntax : instr( rank, string, sub_string )

rank :

  1. Integer type expression giving the position corresponding to the 1st character in the string from which the sub-string search begins.
  2. String : String is your text.
  3. sub_string : The substring which you are looking for. The instr() returns 0 if it does not find the match.

Now how to apply this to the table?. As instr() function is of x3 so its simple to apply.

eg : Filter[ZCT] Where instr(1,ALLOTEDTO,”Ram”) <> 0.

where 1 is the start position to look for sub-string , ALLOTEDTO is column name which consist of String and last parameter is sub-string itself. This will give you all records from table where ALLOTEDTO column consist sub-string “Ram”

which is equivalent to.

Select * from ZCT where ALLOTEDTO like ‘%Ram%’.

Note: Instr() function is case sensitive so always use the upper-case or lower-case function with it.

Vivek Rahul
  • 314
  • 2
  • 15