How to make my code more efficient (beginner :)

Question

can someone suggest a shorter code that does the same?

CASE 
WHEN  {location} LIKE '%00' OR  {location} LIKE  '%010' OR  {location} LIKE '%011' OR  {location} LIKE '%012' OR  {location} LIKE '%013' THEN '1'
WHEN  {location} LIKE '%38' OR  {location} LIKE '%039' OR  {location} LIKE '%040' OR  {location} LIKE '%041' OR  {location} LIKE '%042' OR  {location} LIKE '%043' OR  {location} LIKE '%044' OR  {location} LIKE '%046' OR  {location} LIKE '%047' THEN '2'
ELSE '3' 
END

For efficiency you should see if your DBMS supports the concept of calculated columns or function based indexes and see that you get the last three characters of location indexed and use `=` instead of `LIKE`. Or maybe index the reversed location and reverse the `LIKE` pattern, to get the wildcard to the end. — sticky bit, Dec 29 '18 at 20:41

ScaisEdge · Accepted Answer · 2018-12-29T20:56:21.537

1

instead fo several OR you could try using between range

CASE when substr({location}, -2) ='00' 
  or cast(substr({location}, -2)) AS UNSIGNED )between 10 and 13 then '1'
    when cast(substr({location}, -2)) AS UNSIGNED) between 38 and 47 then '2'
  else '3'
end

edited Dec 29 '18 at 20:56

answered Dec 29 '18 at 20:30

ScaisEdge

131,976
10
91
107

score 0 · Answer 2 · answered Dec 29 '18 at 21:48

If you are using a database that supports regular expressions then you can simplify this considerably. For instance, in MySQL:

(CASE WHEN  {location} REGEXP '00$|010$|011$|012$|013$' THEN '1'
      WHEN  {location} REGEXP '38$|039$|040$|041$|042$|043$|044$|046$|047$' THEN '2'
      ELSE '3' 
 END)

How to make my code more efficient (beginner :)

2 Answers2