Removing special characters from list

Rengudi

Hi

Best time,

I want remove all special to no space ''

CREATE VIEW spc_table(Special,characters) AS
SELECT '-¿---' AS Special,NULL AS characters FROM dual
UNION ALL
SELECT '-/',NULL FROM dual
UNION ALL
SELECT '--¿---------',NULL FROM dual
UNION ALL
SELECT '(¿)',NULL FROM dual
UNION ALL
SELECT '--¿--------',NULL FROM dual
UNION ALL
SELECT '-----¿-',NULL FROM dual
UNION ALL
SELECT '-----¿¿',NULL FROM dual
UNION ALL
SELECT '-(¿)',NULL FROM dual
UNION ALL
SELECT '-¿',NULL FROM dual
UNION ALL
SELECT '()(¿)',NULL FROM dual
UNION ALL
SELECT '---',NULL FROM dual
UNION ALL
SELECT '---',NULL FROM dual
UNION ALL
SELECT '-',NULL FROM dual
UNION ALL
SELECT '-----<-->-----',NULL FROM dual
UNION ALL
SELECT '--/--¿/',NULL FROM dual
UNION ALL
SELECT '-¿:----',NULL FROM dual
UNION ALL
SELECT '----',NULL FROM dual
UNION ALL
SELECT NULL,NULL FROM dual
UNION ALL
SELECT '--¿--------.',NULL FROM dual
UNION ALL
SELECT '¿',NULL FROM dual
UNION ALL
SELECT '-----<-->---',NULL FROM dual
UNION ALL
SELECT '---<¿>---',NULL FROM dual
UNION ALL
SELECT '---¿-..|¿',NULL FROM dual
UNION ALL
SELECT '--..¿--',NULL FROM dual
UNION ALL
SELECT '----<-->----',NULL FROM dual
UNION ALL
SELECT '---..¿--',NULL FROM dual
UNION ALL
SELECT '--¿----:',NULL FROM dual
UNION ALL
SELECT '¿--:',NULL FROM dual
UNION ALL
SELECT '(-)(¿)',NULL FROM dual
UNION ALL
SELECT '-,',NULL FROM dual
UNION ALL
SELECT '(¿)--!!!!!!',NULL FROM dual
UNION ALL
SELECT '--¿--',NULL FROM dual
UNION ALL
SELECT '-----',NULL FROM dual
UNION ALL
SELECT '/',NULL FROM dual
UNION ALL
SELECT '--:-:-',NULL FROM dual
UNION ALL
SELECT '------',NULL FROM dual
UNION ALL
SELECT '---',NULL FROM dual
UNION ALL
SELECT '-----<-->----',NULL FROM dual
UNION ALL
SELECT '----()',NULL FROM dual
UNION ALL
SELECT '-!!!!!!',NULL FROM dual
UNION ALL
SELECT '---<->¿-----',NULL FROM dual
UNION ALL
SELECT '¿¿-:',NULL FROM dual
UNION ALL
SELECT '----<-->---',NULL FROM dual
UNION ALL
SELECT '--',NULL FROM dual
UNION ALL
SELECT '----¿-',NULL FROM dual
UNION ALL
SELECT '---<-->--',NULL FROM dual
UNION ALL
SELECT '-¿()',NULL FROM dual
UNION ALL
SELECT '--¿',NULL FROM dual;

Possible?

Thanks

User_63PY8

with t as(SELECT '-¿---' AS Special,NULL AS characters FROM dual

UNION ALL

SELECT '-/',NULL FROM dual

UNION ALL

SELECT '--¿---------',NULL FROM dual

UNION ALL

SELECT '(¿)',NULL FROM dual

UNION ALL

SELECT '--¿--------',NULL FROM dual

UNION ALL

SELECT '-----¿-',NULL FROM dual

UNION ALL

SELECT '-----¿¿',NULL FROM dual

UNION ALL

SELECT '-(¿)',NULL FROM dual

UNION ALL

SELECT '-¿',NULL FROM dual

UNION ALL

SELECT '()(¿)',NULL FROM dual

UNION ALL

SELECT '---',NULL FROM dual

UNION ALL

SELECT '---',NULL FROM dual

UNION ALL

SELECT '-',NULL FROM dual

UNION ALL

SELECT '-----<-->-----',NULL FROM dual

UNION ALL

SELECT '--/--¿/',NULL FROM dual

UNION ALL

SELECT '-¿:----',NULL FROM dual

UNION ALL

SELECT '----',NULL FROM dual

UNION ALL

SELECT NULL,NULL FROM dual

UNION ALL

SELECT '--¿--------.',NULL FROM dual

UNION ALL

SELECT '¿',NULL FROM dual

UNION ALL

SELECT '-----<-->---',NULL FROM dual

UNION ALL

SELECT '---<¿>---',NULL FROM dual

UNION ALL

SELECT '---¿-..|¿',NULL FROM dual

UNION ALL

SELECT '--..¿--',NULL FROM dual

UNION ALL

SELECT '----<-->----',NULL FROM dual

UNION ALL

SELECT '---..¿--',NULL FROM dual

UNION ALL

SELECT '--¿----:',NULL FROM dual

UNION ALL

SELECT '¿--:',NULL FROM dual

UNION ALL

SELECT '(-)(¿)',NULL FROM dual

UNION ALL

SELECT '-,',NULL FROM dual

UNION ALL

SELECT '(¿)--!!!!!!',NULL FROM dual

UNION ALL

SELECT '--¿--',NULL FROM dual

UNION ALL

SELECT '-----',NULL FROM dual

UNION ALL

SELECT '/',NULL FROM dual

UNION ALL

SELECT '--:-:-',NULL FROM dual

UNION ALL

SELECT '------',NULL FROM dual

UNION ALL

SELECT '---',NULL FROM dual

UNION ALL

SELECT '-----<-->----',NULL FROM dual

UNION ALL

SELECT '----()',NULL FROM dual

UNION ALL

SELECT '-!!!!!!',NULL FROM dual

UNION ALL

SELECT '---<->¿-----',NULL FROM dual

UNION ALL

SELECT '¿¿-:',NULL FROM dual

UNION ALL

SELECT '----<-->---',NULL FROM dual

UNION ALL

SELECT '--',NULL FROM dual

UNION ALL

SELECT '----¿-',NULL FROM dual

UNION ALL

SELECT '---<-->--',NULL FROM dual

UNION ALL

SELECT '-¿()',NULL FROM dual

UNION ALL

SELECT '--¿',NULL FROM dual)select regexp_replace(Special,'[^[:cntrl:]]','') from t;

Frank Kulash

Hi, @Rengudi

I want remove all special to no space ''

What do you mean? Post your general requirements and the specific results you want from the given sample data.

Why do you have a view where one column is always NULL?

mathguy

I don't understand your question (and I am probably not "special" - if I don't understand, probably many others also don't understand).

I want remove all special to no space ''

What does that mean?

"I want to remove" - I understand that, but then you say "... to <something>". I don't get that. When you remove something, you just remove it, you don't remove it "to something".

What does no space '' mean? Do you mean you just want to remove the special characters? Same as replacing them with no space (meaning, with nothing?) Just say "remove special characters", that's all you need to say.

Then, which characters are "special"? Is a question mark like ? a special character? Is a dash - a special character? There is no technical definition of "special character" so if you use that term, you need to say exactly what you mean.

You gave a long list of sample data (I assume). What do you want the end result to be? Why are there two columns, special and characters? What do we need to do with them?

User_63PY8

with t as(SELECT '-¿---' AS Special,NULL AS characters FROM dual

UNION ALL

SELECT '-/',NULL FROM dual

UNION ALL

SELECT '--¿---------',NULL FROM dual

UNION ALL

SELECT '(¿)',NULL FROM dual

UNION ALL

SELECT '--¿--------',NULL FROM dual

UNION ALL

SELECT '-----¿-',NULL FROM dual

UNION ALL

SELECT '-----¿¿',NULL FROM dual

UNION ALL

SELECT '-(¿)',NULL FROM dual

UNION ALL

SELECT '-¿',NULL FROM dual

UNION ALL

SELECT '()(¿)',NULL FROM dual

UNION ALL

SELECT '---',NULL FROM dual

UNION ALL

SELECT '---',NULL FROM dual

UNION ALL

SELECT '-',NULL FROM dual

UNION ALL

SELECT '-----<-->-----',NULL FROM dual

UNION ALL

SELECT '--/--¿/',NULL FROM dual

UNION ALL

SELECT '-¿:----',NULL FROM dual

UNION ALL

SELECT '----',NULL FROM dual

UNION ALL

SELECT NULL,NULL FROM dual

UNION ALL

SELECT '--¿--------.',NULL FROM dual

UNION ALL

SELECT '¿',NULL FROM dual

UNION ALL

SELECT '-----<-->---',NULL FROM dual

UNION ALL

SELECT '---<¿>---',NULL FROM dual

UNION ALL

SELECT '---¿-..|¿',NULL FROM dual

UNION ALL

SELECT '--..¿--',NULL FROM dual

UNION ALL

SELECT '----<-->----',NULL FROM dual

UNION ALL

SELECT '---..¿--',NULL FROM dual

UNION ALL

SELECT '--¿----:',NULL FROM dual

UNION ALL

SELECT '¿--:',NULL FROM dual

UNION ALL

SELECT '(-)(¿)',NULL FROM dual

UNION ALL

SELECT '-,',NULL FROM dual

UNION ALL

SELECT '(¿)--!!!!!!',NULL FROM dual

UNION ALL

SELECT '--¿--',NULL FROM dual

UNION ALL

SELECT '-----',NULL FROM dual

UNION ALL

SELECT '/',NULL FROM dual

UNION ALL

SELECT '--:-:-',NULL FROM dual

UNION ALL

SELECT '------',NULL FROM dual

UNION ALL

SELECT '---',NULL FROM dual

UNION ALL

SELECT '-----<-->----',NULL FROM dual

UNION ALL

SELECT '----()',NULL FROM dual

UNION ALL

SELECT '-!!!!!!',NULL FROM dual

UNION ALL

SELECT '---<->¿-----',NULL FROM dual

UNION ALL

SELECT '¿¿-:',NULL FROM dual

UNION ALL

SELECT '----<-->---',NULL FROM dual

UNION ALL

SELECT '--',NULL FROM dual

UNION ALL

SELECT '----¿-',NULL FROM dual

UNION ALL

SELECT '---<-->--',NULL FROM dual

UNION ALL

SELECT '-¿()',NULL FROM dual

UNION ALL

SELECT '--¿',NULL FROM dual)select regexp_replace(Special,'[^[:cntrl:]]','') from t;

BluShadow

As others have already said, you need to define what you mean by "special" characters, and like Mathguy says, it sounds like you just want to remove them, so "to no space" has no real meaning except perhaps that you don't want to replace them with spaces (which is just the same as removing them).

I assume the 2nd column in your test data indicates you want to list the "special" characters from the string?

So, making an assumption that special characters are something that falls outside a particular range of characters e.g. the standard ANSI readable characters are from chr 32 (space) to chr 126 (~)... but let's assume different character sets and it's just chr 32 to chr 126 you want to consider non-'special'.

SQL> ed
Wrote file afiedt.buf

  1  with t as(
  2    SELECT '-¿---' AS Special,NULL AS characters FROM dual UNION ALL
  3    SELECT '-/',NULL FROM dual UNION ALL
  4    SELECT '--¿---------',NULL FROM dual UNION ALL
  5    SELECT '(¿)',NULL FROM dual UNION ALL
  6    SELECT '--¿--------',NULL FROM dual UNION ALL
  7    SELECT '-----¿-',NULL FROM dual UNION ALL
  8    SELECT '-----¿¿',NULL FROM dual UNION ALL
  9    SELECT '-(¿)',NULL FROM dual UNION ALL
 10    SELECT '-¿',NULL FROM dual UNION ALL
 11    SELECT '()(¿)',NULL FROM dual UNION ALL
 12    SELECT '---',NULL FROM dual UNION ALL
 13    SELECT '---',NULL FROM dual UNION ALL
 14    SELECT '-',NULL FROM dual UNION ALL
 15    SELECT '-----<-->-----',NULL FROM dual UNION ALL
 16    SELECT '--/--¿/',NULL FROM dual UNION ALL
 17    SELECT '-¿:----',NULL FROM dual UNION ALL
 18    SELECT '----',NULL FROM dual UNION ALL
 19    SELECT NULL,NULL FROM dual UNION ALL
 20    SELECT '--¿--------.',NULL FROM dual UNION ALL
 21    SELECT '¿',NULL FROM dual UNION ALL
 22    SELECT '-----<-->---',NULL FROM dual UNION ALL
 23    SELECT '---<¿>---',NULL FROM dual UNION ALL
 24    SELECT '---¿-..|¿',NULL FROM dual UNION ALL
 25    SELECT '--..¿--',NULL FROM dual UNION ALL
 26    SELECT '----<-->----',NULL FROM dual UNION ALL
 27    SELECT '---..¿--',NULL FROM dual UNION ALL
 28    SELECT '--¿----:',NULL FROM dual UNION ALL
 29    SELECT '¿--:',NULL FROM dual UNION ALL
 30    SELECT '(-)(¿)',NULL FROM dual UNION ALL
 31    SELECT '-,',NULL FROM dual UNION ALL
 32    SELECT '(¿)--!!!!!!',NULL FROM dual UNION ALL
 33    SELECT '--¿--',NULL FROM dual UNION ALL
 34    SELECT '-----',NULL FROM dual UNION ALL
 35    SELECT '/',NULL FROM dual UNION ALL
 36    SELECT '--:-:-',NULL FROM dual UNION ALL
 37    SELECT '------',NULL FROM dual UNION ALL
 38    SELECT '---',NULL FROM dual UNION ALL
 39    SELECT '-----<-->----',NULL FROM dual UNION ALL
 40    SELECT '----()',NULL FROM dual UNION ALL
 41    SELECT '-!!!!!!',NULL FROM dual UNION ALL
 42    SELECT '---<->¿-----',NULL FROM dual UNION ALL
 43    SELECT '¿¿-:',NULL FROM dual UNION ALL
 44    SELECT '----<-->---',NULL FROM dual UNION ALL
 45    SELECT '--',NULL FROM dual UNION ALL
 46    SELECT '----¿-',NULL FROM dual UNION ALL
 47    SELECT '---<-->--',NULL FROM dual UNION ALL
 48    SELECT '-¿()',NULL FROM dual UNION ALL
 49    SELECT '--¿',NULL FROM dual
 50    )
 51  select regexp_replace(special,'[^'||chr(32)||'-'||chr(126)||']','') as special_removed
 52        ,regexp_replace(special,'['||chr(32)||'-'||chr(126)||']','') as special
 53* from t
SQL> /


SPECIAL_REMOVED                SPECIAL
------------------------------ ------------------------------
----                           ¿
-/
-----------                    ¿
()                             ¿
----------                     ¿
------                         ¿
-----                          ¿¿
-()                            ¿
-                              ¿
()()                           ¿
---
---                            
-
-----<-->-----
--/--/                         ¿
-:----                         ¿
----

----------.                    ¿
                               ¿
-----<-->---
---<>---                       ¿
----..|                        ¿¿
--..--                         ¿
----<-->----
---..--                        ¿
------:                        ¿
--:                            ¿
(-)()                          ¿
-,
()--!!!!!!                     ¿
----                           ¿
-----
/
--:-:-
------
---                            
-----<-->----
----()
-!!!!!!
---<->-----                    ¿
-:                             ¿¿
----<-->---
--
-----                          ¿
---<-->--
-()                            ¿
--                             ¿

48 rows selected.

Is that the sort of thing you want?

If not, post complete details, and what you actually expect as a result. It's hard for people to give you answers if we don't know what a correct answer will look like.