-4

I need a query to remove all alphanumeric characters from a string and give me only special characters.

If string is '@#45gr@@3' query should give me '@#@@'.

APC
  • 144,005
  • 19
  • 170
  • 281
Debasis Mohapatra
  • 129
  • 3
  • 16
  • regexp_substr,replace everything,but not able to extract special characters – Debasis Mohapatra Feb 22 '18 at 07:27
  • 1
    I’d suggest adding your actual code, and also adding the regex tag to your question – sasfrog Feb 22 '18 at 07:30
  • "not able to extract special characters" is not a precise enough error description for us to help you. *What* doesn't work? *How* doesn't it work? What trouble do you have with your code? Do you get an error message? What is the error message? Is the result you are getting not the result you are expecting? What result do you expect and why, what is the result you are getting and how do the two differ? Is the behavior you are observing not the desired behavior? What is the desired behavior and why, what is the observed behavior, and in what way do they differ? – Jörg W Mittag Feb 22 '18 at 07:34
  • Can you provide a *precise* specification of what it is that you want to happen, including any and all rules, exceptions from those rules, corner cases, special cases, boundary cases, and edge cases? Can you provide sample inputs and outputs demonstrating what you expect to happen, both in normal cases, and in all the exceptions, corner cases, special cases, boundary cases, and edge cases? Please, also make sure to provide a [mcve]. – Jörg W Mittag Feb 22 '18 at 07:34
  • What is the code you are having trouble with? What trouble do you have with your code? Do you get an error message? What is the error message? Is the result you are getting not the result you are expecting? What result do you expect and why, what is the result you are getting and how do the two differ? Is the behavior you are observing not the desired behavior? What is the desired behavior and why, what is the observed behavior, and in what way do they differ? Please, provide a [mcve]. – Jörg W Mittag Feb 22 '18 at 07:34
  • You do it by writing a program which does that. If you have a problem with your program, carefully read the documentation of all the methods, classes, modules, and libraries you are using, write tests for your programs, trace the execution with pen and paper, single-step it in a debugger, then sleep on it, start again from the beginning, sleep on it again, and *then and only then* narrow your problem down to a concise, focused, simple, short, reproducible [mcve] and ask a specific, focused, narrow question on [so]. – Jörg W Mittag Feb 22 '18 at 07:35
  • 1
    Oh @JörgWMittag, you are so on fire.. – sanatsathyan Feb 22 '18 at 07:37
  • I was about to inveigh against @JörgWMittag because I thought he was too much. But you know what, you deserve it. This is a duplicate of a question you asked **two hours ago** to which you got reasonable (but not correct) answers. Please do not ask the same question **repeatedly**. It just wastes peoples' time and pollutes the site. If you don't get the exact answer you need engage with the responders and explain what additional help you need. In short, please show more respect for the people you are asking to help you. – APC Feb 22 '18 at 08:07
  • I closed [the other question](https://stackoverflow.com/q/48920490) because this question does have better answers. Which might seem to undermine my point but actually reinforces it. The right way to get a helpful answer is to ask **one good question** rather than several poor ones. – APC Feb 22 '18 at 08:14

3 Answers3

2
SELECT REGEXP_REPLACE('@#45gr@@3','[^[:punct:]'' '']', NULL) FROM dual;
venkatesh
  • 151
  • 13
1

The old-fashioned way, with a replace() call:

select translate(upper(txt)
                 , '.1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ'
                 , '.') as spec_txt
from t42
/

With a replace() solution is better to type all the characters we want to exclude. Yes that is the larger set (probably) but at least it's well-defined. We don't want to keep revising the code to handle new characters in the input string.

Obviously regex solution is more compact and who can deny the elegance of @venkatesh solution? However, evaluating regular expressions is more expensive than more focused SQL functions, so it's worth knowing alternative ways of doing things, for those cases when performance is an issue.

APC
  • 144,005
  • 19
  • 170
  • 281
0

Everything written in comments is most probably very true (especially the 5th one that talks about exceptions, special cases etc.). My feeling is that Jörg knows more about regular expressions than I'll ever know.

Anyway, to cut a long story short, in its very simple appearance, regarding the question posted ("remove all numbers and letters"), something like this might work:

SQL> select regexp_replace('a@#45gr@@3$.', '[[:digit:]]+|[[:alpha:]]+', '') result
  2  from dual;

RESULT
------
@#@@$.

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57