is it possible to create an index using substr ?

say for example i have a field called num which has char(8) as its datatype and length.

an example of num's value would be something like 09092010

now what i want is to create an index using the last 4 characters of num's value which is 2010 in my example.

would it be possible to do it?

Edited by charvie: n/a

6 Years
Discussion Span
Last Post by smantscheff

You can create an indexed 4-character field and update it using a trigger.

what do you mean trigger?
i just want it to be a plain mysql create index query.

something like this

create index nmbr on nmbr_table (substr(number,1,2))

they say this line is possible in oracle.
is this possible in mysql?


To the best of my knowledge this is not possible in MyQL.
A trigger is a procedure which is automatically executed on standard table operations like insert, update and delete. Such a trigger could copy the desired character string to the indexed field.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.