RE: Search and replace query and search query

>Hi List,
>
> I have a couple of queries to be written in
>SQL:
>
> I have a table with the values as SK 05100
>and LH 07244. I have to
>update these values with SK5100 and LH7244. That
>means I have to remove the
>white space in the 3rd position and the 0 in the
>fourth position. How do I
>do this?

  substr(code, 1, 2) || ltrim(substr(code, 4), '0)

I assume you always have a space in 3rd position but can have something else than a 0 in 4th.

> I have a table with a column as weekdays
>with datatype as number(7)
>and the values can be any value from 1-7. For
>example, 345, 456, 567,
>1234567 etc.
>I have to find out if the value that is supplied as
>input shouldn't exist in
>the table. That is if I pass a value as 123 any of
>1 or 2 or 3 shouldn't
>exist in the table in any position. How do I check
>this?
>
> Any help in this regard is very much
>appreciated.
>
> Thanks and Regards,
>
> Ranganath
>

Check

   instr(translate(ltrim(to_char(column_to_check)),

                   ltrim(to_char(your_input)),
                   rpad('*', length(ltrim(to_char(your_input))), '*')), '*')

 First check the SQL documentation about all these functions ...

Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).