1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle SQL Remove Special Characters

Discussion in 'SQL PL/SQL' started by ksjj, Oct 27, 2010.

  1. ksjj

    ksjj Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Hi all,

    I have a table with values like

    A.1234
    2589
    345689-
    25874+
    245.
    A.125897-

    There are special characters in some of the values at the end of the value. I want to remove those characters .

    could any one let me know how to do it.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: removing of special characters from data

    That depends; do you want all of these values to be numbers?
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Re: removing of special characters from data

    Replace will be one option for you.

    Code (SQL):
    WITH REC AS (
    SELECT '25899974+' X FROM DUAL)
    SELECT  SUBSTR(X,1,LENGTH(X)-1)||REPLACE(REPLACE(SUBSTR(X,-1),'+'),'.') FROM  REC
     
    again, Regular expression will the best option , i guess.

    Regular expression
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Re: removing of special characters from data

    How about using the translate function ??
     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Try this....

    select regexp_replace(col_name, '( *[[:punct:]])', ' ') test_string from table_name
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    if necessary to delete only the special character from a line end, then it is possible to try this option :

    Code (SQL):

    WITH
    sample_data AS
    (SELECT 'A.1234' a FROM dual UNION ALL
    SELECT '2589' FROM dual UNION ALL
    SELECT '345689-' FROM dual UNION ALL
    SELECT '25874+' FROM dual UNION ALL
    SELECT '245.' FROM dual UNION ALL
    SELECT 'A.125897-' FROM dual
    )

    SELECT
           sd.a,
           regexp_replace(sd.a,'\W+?$') a_
    FROM sample_data sd;

    SQL>
     
    A         A_
    --------- ------------------------------------
    A.1234    A.1234
    2589      2589
    345689-   345689
    25874+    25874
    245.      245
    A.125897- A.125897
     
    6 ROWS selected
     
     
     
  7. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    If by "special characters" your mean non-printing/control characters, then again regexp_replace will do the job:

    Code (SQL):
    SELECT
    regexp_replace('This is @ test BEL '||chr(7)|| 'ting',  '[[:cntrl:]]') FROM dual;
    Regards,
    Rajen.