Hi
I have a table MYTABLE that contains column VRID
VRID is a char(10) alphanumeric ID. All values should be all upper case, and with no spaces. But sometimes incorrect values have slipped in.
Correct:
AB123CD
Incorrect - should have been AB123CD:
ab123cd
AB123 CD
Ab123cd
etc...
In order to match on this column using a correct value, matching any of the possible incorrect values, I do this:
If I wanted to make this search efficient, am I right in thinking it would it be as simple as creating this index:
And the SQL processor will recognise that there is an index matching the exact scalar transform I am doing on column VRID in my select statement, and use the index?
I have a table MYTABLE that contains column VRID
VRID is a char(10) alphanumeric ID. All values should be all upper case, and with no spaces. But sometimes incorrect values have slipped in.
Correct:
AB123CD
Incorrect - should have been AB123CD:
ab123cd
AB123 CD
Ab123cd
etc...
In order to match on this column using a correct value, matching any of the possible incorrect values, I do this:
Code:
exec sql select * from MYTABLE where upper(replace(VRID,' ','')) = :somevalue;
Code:
create index MYTABLEI1 on MYTABLE (upper(replace(VRID,' ','')));
Comment