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
How to remove special characters from a string
Moderator: NorbertKrupa
-
- Newbie
- Posts: 1
- Joined: Mon Oct 13, 2014 11:57 am
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: How to remove special characters from a string
You can try the REGEXP_REPLACE function.
Example:
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?
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.