Re: Regular expression help required please
Date: Tue, 21 Sep 2010 06:18:41 -0700 (PDT)
Message-ID: <5fbe3097-1e25-4ae8-94db-0460b539a8ae_at_v23g2000vbi.googlegroups.com>
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? Received on Tue Sep 21 2010 - 08:18:41 CDT