Re: how to trim off text

susana73_at_hotmail.com (Susan Lam) wrote in message news:<7186ed56.0305231017.4fe5b68d_at_posting.google.com>...
> I have a varchar2 column have data like below:
> 001
> 002
> 003-ABC
> 004-XYZ
> 005
>
> I would like to trim off everything after '-', resulting in
> 001,002,003,004,005. How do I do that? I tried rtrim but it looks
> like it can only trim off a fix value of text..
>
> Thanks in advance,
> Susan

I am not at a computer where I can access Oracle to test code but here is what you can do: Look up instr, length, and substr in the SQL manual.

You can use a case statement to return those columns without a dask as they are and to return the value minus the trailing characters by using instr and substr functions. Instr will return the location of the dask so you can substr(col,1,instr(col)) to get the first x characters.

I am not sure if your listing of the values as "001,002,..." means was to just clarify how the final values should appear individualy of if you also want to transform the output of multiple rows into one row. That is a different problem.

HTH -- Mark D Powell -- Received on Fri May 23 2003 - 19:15:33 CDT