2

I am using 'punct' function to replace special characters in a String ex: ' REPLACE (REGEXP_REPLACE (colum1, '[[:punct:]]' ), ' ', '')) AS OUPUT ' as part of SQL String in java, But I want particular special character '-' not to be replaced? can you suggest best way to do this?

crazyforjava
  • 65
  • 2
  • 9
  • If you are dealing with ascii strings, Instead of using `[[:punct:]]`, build a character class with the exhaustive list of character. Note that your question is more about SQL. – Casimir et Hippolyte Oct 10 '16 at 21:42

1 Answers1

2

Acc. to Character Classes and Bracket Expressions:

‘[:punct:]’
Punctuation characters; in the ‘C’ locale and ASCII character encoding, this is ! " # $ % & ' ( ) * + , - . / : ; < = > ? @ [ \ ] ^ _ \ { | } ~.

Hence, use

[][!"#$%&'()*+,./:;<=>?@\\^_`{|}~]

Make sure you escape the ' correctly in the string literal.

A shortened expression with ranges will look like

[!-,.-/:-@[-`{-~]

See a regex test here (the - is between , and ., thus you need to use two !-,.-/ ranges in the above expression to exclude the hyphen).

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
  • Hi @Wiktor Stribiżew thanks for your response, But I am trying to achieve this in SQL ex:DECLARE colum1 VARCHAR2(20):='ABCD-EFG'; CHARW VARCHAR(20); BEGIN CHARW :=REPLACE (REGEXP_REPLACE (colum1, '[[:punc:]' ), ' ', ''); dbms_output.put_line('Printing'||CHARW); END; – crazyforjava Oct 12 '16 at 15:50
  • Great, then use the expression I suggest. Also, note that `[[:punc:]` contains a typo, it must be `[[:punc:]]`. Anyway, atoms cannot be subtracted from POSIX regex patterns (because there is no Java like `[\w&&[^\d]]` or .NET like `[\p{P}-[,]]`), so you need to rewrite it as I show. – Wiktor Stribiżew Oct 12 '16 at 15:52
  • and I don't want particular character '-' to be deleted in above input SQL i.e,.. 'ABCD-EFG' – crazyforjava Oct 12 '16 at 15:54
  • Yes, I see, I forgot to exclude it, I have edited the ranges now. So, this `[][!"#$%&'()*+,./:;<=>?@\\^_\`{|}~]` should work for you. And `[!-,.-/:-@[-\`{-~]` will work for you, too. – Wiktor Stribiżew Oct 12 '16 at 15:55
  • Thank you. Just tried this REPLACE (REGEXP_REPLACE (colum1, '[!/:-@[-`{-~]' ), ' ', '');...it worked but will this expression take care of removing all special characters except particular one '-' @Wiktor Stribiżew – crazyforjava Oct 12 '16 at 16:03
  • this [!-,.-/:-@[-`{-~] expression worked perfect and took care of all except '-'....thanks for your help:) @Wiktor Stribiżew – crazyforjava Oct 12 '16 at 16:08
  • Glad it worked for you. Please also consider upvoting if my answer proved helpful to you (see [How to upvote on Stack Overflow?](http://meta.stackexchange.com/questions/173399/how-to-upvote-on-stack-overflow)) since after reaching 15 rep points you obtained the upvoting privilege. – Wiktor Stribiżew Dec 06 '16 at 14:22