Re: How to cut a field in oracle

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.

 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.

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool