Oracle Query Notes (Part 2): INSTR, LPAD, and RPAD

Table of contents

Article body

1. The problem: we often use LIKE in queries. Both MySQL and Oracle support pattern matching with LIKE, for example:

t.name like ‘%张三%’. Today I discovered Oracle’s INSTR, which can serve a similar purpose to LIKE and, in my experience here, is much more efficient. Let’s look at this useful function.

instr(string1,string2,[START_POSITION, OCCURRENCE_TO_FIND])

INSTR takes four parameters. The first is the source string, and the second is the substring to find. The last two are the starting position and the occurrence to find, shown above. I usually use only the first two.

INSTR returns the position of the matching substring in the source string. For example:

select instr(‘abc’,‘a’) from t    returns 1; positions are counted from 1

select instr(‘abca’,‘a’,2) from t  returns 4; the search for a starts at the second position

select instr(‘abc’,‘a’,2,2) from t returns 0; no match returns 0

To filter rows in a similar way to LIKE, use it in a WHERE condition:

select * from t where instr(t.name，‘张三’) >0 has the same effect here as t.name like ‘%张三%’.

For a NOT LIKE equivalent, change the condition to =0.

Note: to perform this LIKE-style filtering, put the expression in the WHERE condition.

I haven’t yet worked out why INSTR is more efficient than LIKE in this case.

2. Another problem: while using a sequence for auto-incrementing values in a row-level trigger, I came across an Oracle function I hadn’t used before: LPAD.

LPAD pads from the left: lapd(STRING_TO_PAD, TARGET_LENGTH[length], PADDING_VALUE). For example:

lpad(projectcode.NEXTVAL,8,0) pads the value to eight characters using zeros.

The corresponding RPAD function pads from the right.