×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
Hi James,
Searches for 01, 02, 03, and 04 would all hit, but so would searches for
10, 20, and 30. Is there a practical way to prevent the spurious hits,
other than to bite the bullet and allow space for delimiters?
With or without spaces, the way you're doing it is not a great idea.
Why? Because it's going to require a full table scan -- there's no way
to build an index to improve search times.
The "right" way to do this is to have your codes stored in a separate
table, one record per code. That way, you can join the tables, and have
indexes on the fields to improve performance.
But let's assume you don't want to do that, and that there are only a
small number of records, so a full table scan doesn't matter to you...
a) You could use SUBSTR.
select blah,blah from mytable
where substr(field1,1,2)='01'
or substr(field1,3,2)='01'
or substr(field1,5,2)='01'
or substr(field1,7,2)='01'
or substr(field1,9,2)='01'
b) Since the above SUBSTR is cumbersome. You could write a UDF. The
UDF would receive the field containing the list, and the code(s) to look
for as parameters. It would then return whether it was found or not.
select blah,blah from mytable
where MYUDF(field1,'01')=1
midrange.com
