From within an Oracle 11g database, using SQL, I need to remove the following sequence of special characters from a string, i.e.
~!@#$%^&*()_+=\{}[]:”;’<,>./?
If any of these characters exist within a string, except for these two characters, which I DO NOT want removed, i.e.: "|" and "-" then I would like them completely removed.
For example:
From: 'ABC(D E+FGH?/IJK LMN~OP' To: 'ABCD EFGHIJK LMNOP' after removal of special characters.
I have tried this small test which works for this sample, i.e:
select regexp_replace('abc+de)fg','\+|\)') from dual
but is there a better means of using my sequence of special characters above without doing this string pattern of '\+|\)' for every special character using Oracle SQL?
Braj :
You can replace anything other than letters and space with empty string\n\n[^a-zA-Z ]\n\n\nhere is online demo\n\n\n\nAs per below comments\n\n\n I still need to keep the following two special characters within my string, i.e. \"|\" and \"-\".\n\n\nJust exclude more\n\n[^a-zA-Z|-]\n\n\nNote: hyphen - should be in the starting or ending or escaped like \\- because it has special meaning in the Character class to define a range.\n\nFor more info read about Character Classes or Character Sets",
2014-08-15T15:33:46
Unihedron :
Consider using this regex replacement instead:\n\nREGEXP_REPLACE('abc+de)fg', '[~!@#$%^&*()_+=\\\\{}[\\]:”;’<,>.\\/?]', '')\n\n\nThe replacement will match any character from your list.\n\nHere is a regex demo!",
2014-08-15T15:34:04
Andie2302 :
The regex to match your sequence of special characters is:\n\n[]~!@#$%^&*()_+=\\{}[:”;’<,>./?]+\n",
2014-08-15T18:41:43
user2999951 :
I feel you still missed to escape all regex-special characters.\nTo achieve that, go iteratively:\nbuild a test-tring and start to build up your regex-string character by character to see if it removes what you expect to be removed.\nIf the latest character does not work you have to escape it.\nThat should do the trick.",
2015-05-12T07:32:09
Toolkit :
SELECT TRANSLATE('~!@#$%sdv^&*()_+=\\dsv{}[]:”;’<,>dsvsdd./?', '~!@#$%^&*()_+=\\{}[]:”;’<,>./?',' ')\nFROM dual;\n\n\nresult:\n\nTRANSLATE\n-------------\n sdvdsvdsvsdd\n",
2017-01-19T19:15:56
Ali Panjmir :
You can use this:\nregexp_replace('ABC(D E+FGH?/IJK LMN~OP','\\W')\n\nThis type of input in regexp_replace function, will easily revised by \\W value.\nAlso, the input value of \\w can keep special chars and remove (or replace) all alphabetical chars.",
2023-03-27T09:28:39