How to remove special characters from a string

Moderator: NorbertKrupa

Post Reply
srinivas.martha83
Newbie
Newbie
Posts: 1
Joined: Mon Oct 13, 2014 11:57 am

How to remove special characters from a string

Post by srinivas.martha83 » Mon Oct 13, 2014 12:17 pm

Hi Friends,

I have some requirement where we need to remove the special characters from a string column.

1st Scenario: Data coming with special characters like '#','$',']',',','"','%','.','\' should be removed from both starting position and ending position.

examples of wrong data:

,.,ABC_ABCD,., ------Incorrect Data
?/EFg#AB789,., ------Incorrect Data
"%WER#H#JTR,., ------Incorrect Data

(i.e all the special characters starting with should be removed)

special characters at the begining and ending of the data are to be removed but special characters in between the data are allowed.

AB,C_AB#CD ------correct Data
0B,C_1B#C3 ------correct Data

This data is for one of the column with data type CHAR(30).
And Length of correct data should be of length >= 10 characters.
Can some one help me to solve this issue or suggest me for any functions.

Thanks in advance
Srinivas

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How to remove special characters from a string

Post by JimKnicely » Sun Oct 19, 2014 3:19 pm

You can try the REGEXP_REPLACE function.

Example:

Code: Select all

dbadmin=> select * from jim;
      col1
----------------
 ?/EFg#AB789,.,
 ,.,ABC_ABCD,.,
 "%WER#H#JTR,.,
(3 rows)

dbadmin=> select col1, regexp_replace(col1, '^[^a-zA-Z0-9\\s]+|[^a-zA-Z0-9\\s]+$', '') from jim;
      col1      | regexp_replace
----------------+----------------
 ?/EFg#AB789,., | EFg#AB789
 ,.,ABC_ABCD,., | ABC_ABCD
 "%WER#H#JTR,., | WER#H#JTR
(3 rows)
Not sure what you mean with the "And Length of correct data should be of length >= 10 characters." requirement... Do you want to pad the results with spaces so that the length is 10 characters?
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica”