Re: How to cut a field in oracle

Andy Hassall a écrit :

> On 11 Jan 2006 16:59:49 -0800, balu422_at_yahoo.com wrote:
>
> >I am using oracle 8i and I have a table called ge_module_access which
> >has a field name called functions. functions field has values like
> >'NNNNNNYNNNYNYYYY'
> >'YYYNNNYYYNNNNNYY'
> >'YYYYYYNNNNNNYYYY' etc
> >each character means something is there a way I can
> >cut this field in the where clause?
> >select * from ge_module_access
> >where first character of field functions is 'Y';
> >which should bring up 2nd and 3rd record in the
> >example above.
> >BTW I need to cut the field instead of using like.
> >Example:
> >if 'YYNNYY' is function_id 1, 2 and 5, 6.
> >if 'NNYYNN then funcion_id 3 and 4 and so forth....
> >Any help to accomplish the task is appreciated.
>
> SUBSTR is probably the function you're after, e.g
> where substr(ge_module_access, n, 1) = 'Y'
>
> Storing multiple values in a single field breaks First Normal Form so if you
> have any control over the schema you might want to reconsider it - you've got
> 26 characters there representing 26 pieces of Boolean data, so that may be
> better as 26 columns in 1 row, or 26 rows. But this is just an observation from
> seeing the small part of the schema you've posted, so it may be out of context.
Sure it breaks it ;)
A way to conciliate is to use varray typed columns. The OP might consider replacing the varchar2(26) column with a varray of char(1) to a max size of 26. Querying the varray would be easier than substr the varchar2 field and would be more scalable.

> The main thing to bear in mind here is that searching for substrings within
> this sort of data will almost inevitably lead to full table scans, unless you
> do some excessively complex indexing using function based indexes.
I'm picturing lots of FB indexes covering any combination of the 26 bits :)
For the sake of index management, it's definitely not an option.

-- 
SL