Re: SQL searching in a field that has a series of codes

[Scott Klement <midrange-l@xxxxxxxxxxxxxxxx>]

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