Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to remove tabs, multiple spaces, quotes from column data.

Karthik417Mar 22 2014 — edited Mar 23 2014

Hello,

How can we remove tabs, multiple spaces, quotes from column data.

Input:

H     ope you "are" d'oing  w  ell

Output:

H ope you are doing w ell -- There is a tab in Hope and double space in well. This should be replaced by space. (Tab and multi spaces)

I ended up with below:

REGEXP_REPLACE(REGEXP_REPLACE(REPLACE(Col,chr(9),' '), '( ){2,}', ' '),'["'']','')

Can we do it more efficiently, Your inputs are appreciated.

I tried

REGEXP_REPLACE(Col,'[^[a-z,A-Z,0-9,chr(0)-chr(127)[:space:]]]*','')  but tabs still exist and as my data have German characters I cannot go with this.

Thanks

GVK.
This post has been answered by Frank Kulash on Mar 23 2014
Jump to Answer

Comments

Frank Kulash 
Karthik417 wrote:




Hello,




How can we remove tabs, multiple spaces, quotes from column data.




Input:




H     ope you "are" d'oing  w  ell




Output:


H ope you are doing w ell -- There is a tab in Hope and double space in well. This should be replaced by space. (Tab and multi spaces)




I ended up with below:


REGEXP_REPLACE(REGEXP_REPLACE(REPLACE(Col,chr(9),' '), '( ){2,}', ' '),'["'']','')




Can we do it more efficiently, Your inputs are appreciated.




I tried


REGEXP_REPLACE(Col,'[^[a-z,A-Z,0-9,chr(0)-chr(127)[:space:]]]*','')  but tabs still exist and as my data have German characters I cannot go with this.




Thanks


GVK.

Hi,

You can take care of the spaces and tabs in the same REGEXP_REPLACE.  For the single-quotes, I think you'll need another function, but it doesn't need to be a regular expression function.  Plain old REPLACE is more efficient:

REPLACE ( REGEXP_REPLACE ( col


                         , '[ ' || CHR (9) || ']+'


                         , ' '


                         )


        , ''''


        )

If you'd care to post CREATE TABLE and INSERT statements for a little sample data (maybe 5 or 10 rows) and the results you want from that sample data, then I could test this.  Include examples of single-quotes and spaces mixed together, such as:

abd '  '    def
1 person found this helpful
Karthik417

Hello,

Thanks for your suggestion. In above expression, we still missed to replace double quotes.

So was thinking we need to use another regular expression instead of REPLACE.

select REGEXP_REPLACE ( REGEXP_REPLACE ( 'H     ope you "are" d''oing  w  ell ', '[ ' || CHR (9) || ']+', ' '),'[''"from dual;

I guess we have to use at least 2 REGEXP to achieve it and cannot get it with single expression.

Thanks

Frank Kulash
Answer

Hi,

Karthik417 wrote:




Hello,




Thanks for your suggestion. In above expression, we still missed to replace double quotes.




So was thinking we need to use another regular expression instead of REPLACE.








select REGEXP_REPLACE ( REGEXP_REPLACE ( 'H     ope you "are" d''oing  w  ell ', '[ ' || CHR (9) || ']+', ' '),'[''"from dual;




I guess we have to use at least 2 REGEXP to achieve it and cannot get it with single expression.




Thanks

Sorry, I removed single-quotes only, not double-quotes.  (This shows one reason why having a few rows of sample data is so useful.)

I think you do need 2 separate functions.  I don't think they both need to be REGEXP_REPLACE; you can use TRANSLATE to remove both kinds of quotes in a single function call:

TRANSLATE ( REGEXP_REPLACE ( col


                           , '[ ' || CHR (9) || ']+'


                           , ' '


                           )


          , '?'''"',


          , '?'


          )

Of course, you can use use REGEXP_REPLACE to remove the quotes, if you really want to.

As powerful as they are, regular expression functions basically do only 1 thing, not 2  or more different things.  Sometimes, it can look like they're doing multiple things, because they can operate on classes of characters, such as the set containing <space> and <tab>, and variable numbers of characters.  For example, the REGEXP_REPLACE function I used above is just changing expression x to expression y, where x is 1 or more consecutive characters from a given set.  As we both demonstrated, we can have a separate function that changes p to q, but I don't know of any built-in function that, in general, can change x to y at the time that it changes p to q.  TRANSLATE comes close, but TRANSLATE only works if x, y, p and q are single characters (or NULL), not expressions.

Marked as Answer by Karthik417 · Sep 27 2020
Karthik417

Thanks Frank for your kind help and great explanation.

Keep up the good work.

Cheers

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 20 2014
Added on Mar 22 2014
#oracle, #query
4 comments
2,477 views