CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11goracle10g
spyga
spyga

Reputation: 149

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.

Upvotes: 3

Views: 6512

Answers (3)

LifeOfPi
LifeOfPi

Reputation: 625

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

Upvotes: 1

David Faber
David Faber

Reputation: 12486

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.

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

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

Upvotes: 4

Related Questions