Need to trim blank space in oracle

问题:

Unable to trim blank space in below data in my table. column data type is VARCHAR2(650 CHAR). I tried trim function to eliminate blank space. but that is not working for me.

DATA in my table:

'xxxxxxxxxx yyyyyyyyy - 12/7/14 - 12/13/14   '
'xxxxxxxxxx yyyyyyyyy - 12/7/14 - 12/13/14       '
'xxxxxxxxxx yyyyyyyyy - 12/7/14 - 12/13/14   '

I want trim the data like below

'xxxxxxxxxx yyyyyyyyy - 12/7/14 - 12/13/14'
'xxxxxxxxxx yyyyyyyyy - 12/7/14 - 12/13/14'
'xxxxxxxxxx yyyyyyyyy - 12/7/14 - 12/13/14'

Could you please help me on this.

回答1:

Use Trim Function

select Trim(trailing ' ' from 'xxxxxxxxxx yyyyyyyyy - 12/7/14 - 12/13/14   ') from dual

or Use Rtrim

select RTrim('xxxxxxxxxx yyyyyyyyy - 12/7/14 - 12/13/14   ') from dual

回答2:

I would use REGEXP_REPLACE() in the event that there were other whitespace charaters like tabs in there:

SELECT REGEXP_REPLACE(mydata, '\s+$') AS mydata
  FROM mytable

Hope this helps. If TRIM() isn't working then I suspect you have something other than spaces trailing.

If you know that the data is going to end with a digit, you might also try the following:

SELECT REGEXP_REPLACE(mydata, '\D+$') AS mydata
  FROM mytable

The \D in the regex matches everything but a digit, so any trailing characters not a digit 0-9 will be trimmed.

回答3:

We also can trim left and right side blank spaces using below approach

SELECT LTrim(RTrim('xxxxxxxxxx yyyyyyyyy - 12/7/14 - 12/13/14   ')) FROM dual