This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,964 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

Removing special characters from list

Rengudi
Rengudi Member Posts: 594 Bronze Badge
edited Dec 1, 2022 10:05PM in SQL & PL/SQL

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
Tagged:

Best Answer

  • User_63PY8
    User_63PY8 Member Posts: 3 Green Ribbon
    Answer ✓

    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;

    Rengudi

Answers

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond

    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
    mathguy Member Posts: 11,041 Black Diamond

    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?

    Mohamed Houri
  • User_63PY8
    User_63PY8 Member Posts: 3 Green Ribbon
    Answer ✓

    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;

    Rengudi
  • BluShadow
    BluShadow Mr UKMember, Moderator Posts: 42,706 Red Diamond

    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.