Re: Regular expression help required please

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Tue, 21 Sep 2010 07:38:42 -0700 (PDT)
Message-ID: <b69ac240-9783-4256-84f9-4b4b9dd83cf3_at_g18g2000vbn.googlegroups.com>



On Sep 21, 9:18 am, Kevin S <sear..._at_googlemail.com> wrote:
> Firstly I realise that I typed the wrong regular expression in my
> question which won't help so apologies for that.
>
> Perhaps it would be easier if I say that my aim is to strip out
> everything except alphanumeric characters and whitespaces from strings
> to get over an error we are getting in an Oracle 8 database.
>
> We can reproduce the error as follows where step 4 results in the
> 'ORA-01401: inserted value too large for column' error even though
> it's the right length.
>
> 1 DROP TABLE FIFTYCHAR;
> 2 CREATE TABLE FIFTYCHAR(FIFTY_CHAR_COL VARCHAR2(50));
> 3 SELECT length('•    123456789012345678901234567890123456789012345678')
> FROM dual;
> 4 INSERT INTO FIFTYCHAR(FIFTY_CHAR_COL)
>    VALUES('•  123456789012345678901234567890123456789012345678');
> 5 DROP TABLE FIFTYCHAR;
>
> I think I have found an initial solution to my proposed solution.
>
> SELECT  regexp_replace('H\el£lo World','[^[:alnum:][:space:]]', '')
> FROM dual;
>
> will strip out the alpha numerics while leaving white spaces.
>
> It's not ideal as it strips out £ signs and all punctuation, however
> it's characters that fall under the punctuation category (Bullet
> points, £ signs etc) which are causing the problem.
>
> Would anyone have any suggestions as to how to only strip out
> troublesome characters?

If you cannot figure it out using regular expressions alone then you might consider falling back to using the traditional replace function to locate and eliminate specific characters (replace Euro symbol with null, etc...).

HTH -- Mark D Powell -- Received on Tue Sep 21 2010 - 09:38:42 CDT

Original text of this message