CODEWITHSUNDEEP
sqlregexoracle-database
Mariana
Mariana

Reputation: 359

Oracle remove special characters

I have a column in a table ident_nums that contains different types of ids. I need to remove special characters(e.g. [.,/#&$-]) from that column and replace them with space; however, if the special characters are found at the beginning of the string, I need to remove it without placing a space. I tried to do it in steps; first, I removed the special characters and replaced them with space (I used REGEXP_REPLACE) then found the records that contain spaces at the beginning of the string and tried to use the TRIM function to remove the white space, but for some reason is not working that.

Here is what I have done

Select regexp_replace(id_num, '[:(),./#*&-]', ' ') from ident_nums

This part works for me, I remove all the unwanted characters from the column, however, if the string in the column starts with a character I don't want to have space in there, I would like to remove just the character, so I tried to use the built-in function TRIM.

update ident_nums
  set id_num = TRIM(id_num)

I'm getting an error ORA-01407: can't update ident_nums.id_num to NULL

Any ideas what I am doing wrong here?

It does work if I add a where clause,

update ident_nums
  set id_num = TRIM(id_num) where id = 123;

but I need to update all the rows with the white space at the beginning of the string.

Any suggestions are welcome. Or if it can be done better. The table has millions of records.

Thank you

Upvotes: 1

Views: 5784

Answers (5)

Himanshu Kandpal
Himanshu Kandpal

Reputation: 1608

this should work for you.

SELECT id_num, length(id_num) length_old, NEW_ID_NUM, length(NEW_ID_NUM) len_NEW_ID_NUM, ltrim(NEW_ID_NUM), length(ltrim(NEW_ID_NUM)) length_after_ltrim
FROM (
    SELECT id_num, regexp_replace(id_num, '[:(),./#*&-@]', ' ') NEW_ID_NUM FROM 
    (
        SELECT '1234$%45' as id_num from dual UNION 
        SELECT '@SHARMA' as id_num from dual UNION 
        SELECT 'JACK TEST' as id_num from dual UNION 
        SELECT 'XYZ#$' as id_num from dual UNION 
        SELECT '@ABCDE()' as id_num from dual   -- THe 1st character is space
    )
)

Upvotes: 0

Mariana
Mariana

Reputation: 359

Thanks, everyone, It this query worked for me

update update ident_nums
  set id_num = LTRIM(REGEXP_REPLACE(id_num, '[:space:]+', ' ') 
  where REGEXP_LIKE(id_num, '^[ ?]')

Upvotes: 0

Monika Lewandowska
Monika Lewandowska

Reputation: 486

Regexp can be slow sometimes so if you can do it by using built-in functions - consider it.

As @Abra suggested TRIM and TRANSLATE is a good choice, but maybe you would prefer LTRIM - removes only leading spaces from string (TRIM removes both - leading and trailing character ). If you want to remove "space" you can ommit defining the trim character parameter, space is default.

select 
     ltrim(translate('#kdjdj:', '[:(),./#*&-]', ' ')) 
from dual;


select 
     ltrim(translate(orginal_string, 'special_characters_to_remove', ' ')) 
from dual;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I think trim() is the key, but if you want to keep only alpha numerics, digits, and spaces, then:

select trim(' ' from regexp_replace(col, '[^a-zA-Z0-9 ]', ' ', 1, 0))

regexp_replace() makes it possible to specify only the characters you want to keep, which could be convenient.

Upvotes: 1

Abra
Abra

Reputation: 20914

Combination of Oracle built-in functions TRANSLATE and TRIM worked for me.

select trim(' ' from translate('#$one,$2-zero...', '#$,-.','     ')) as RESULT
  from DUAL

Refer to this dbfiddle

Upvotes: 1

Related Questions