Re: help with sql statement doing substrings and replace

efeigenb_at_alumni.tufts.edu (Figz) wrote in message news:<3758d0a5.0303071240.659a3f24_at_posting.google.com>...
> Hi,
>
> I have columns called FILES with data like
>
> FILES
> -----------------------
> 'hello.pdf'
> 'something.pdf'
> 'noextension'
> 'test01.pdf'
> 'anotherwithnoextension'
>
> and I want to find all the ones in that column that have '%.pdf' in
> them and replace them with everything BUT the .pdf extension. So I
> would end up with:
>
> FILES
> -----------------------
> 'hello'
> 'something'
> 'noextension'
> 'test01'
> 'anotherwithnoextension'
>
> Can someone tell me a sql statement to do that? Thanks!

UPDATE My_Table
SET files = SUBSTR(files, 1, LENGTH(files) - 4) WHERE UPPER(files) LIKE '%.PDF';

Regards
/Rauf Sarwar Received on Sat Mar 08 2003 - 07:05:58 CET