Thanks for the quick reply Tom. The answer to you questions is I found it to be inconsistent regarding spaces at the end of the field or not.

I had basically used a very similar sql statement but couldn't get it to work, it kept erroring out.
(select case when TO_NUMBER(translate(substr(trim(d), instr(trim(d),' ',-1) + 1) , '0123456789','0000000000')) = 0 then trim(substr(trim(d), 1, instr(trim(d),' ',-1))) else trim(d) end
from a)


However, I used yours and it ran but the data looks exactly as if I did a select *.

are you sure?

ops$tkyte@ORA10GR2> select * from t;

X
--------------------------------------------------
2003 HOL PERM FIX         04069510367
2003 HOL STD LG FIX   040695103327
2004 HOL FL LG FIX
HOL 2004 SM FIX

ops$tkyte@ORA10GR2> select case when replace( trim(translate( substr( x, instr(rtrim(x),' ',-1)+1 ), '0123456789','0000000000')), '0', '' ) is null
  2              then substr( x, 1, instr(rtrim(x),' ',-1) )
  3              else x
  4          end y
  5  from t;

Y
--------------------------------------------------
2003 HOL PERM FIX
2003 HOL STD LG FIX
2004 HOL FL LG FIX
HOL 2004 SM FIX


 

The following sql removes numbers starting at the end of the string and working back to the first non-number.

SELECT
SUBSTR( x, 1, LENGTH( RTRIM( TRANSLATE( x, '0123456789', '0000000000' ), '0' ) ) )
FROM t


The following sql removes numbers and whitespace starting at the end of the string and working back to the first non-number/non-whitespace character.

SELECT
SUBSTR( x, 1, LENGTH( RTRIM( TRANSLATE( x, '0123456789' ) ) ) )
FROM t

char(50) :)

ops$tkyte@ORA10GR2> SELECT
  2    SUBSTR( x, 1, LENGTH( RTRIM( TRANSLATE( x, '0123456789', '0000000000' ), '0' )
  3  ) )
  4  FROM t
  5  /

SUBSTR(X,1,LENGTH(RTRIM(TRANSLATE(X,'0123456789','
--------------------------------------------------
2003 HOL PERM FIX         04069510367
2003 HOL STD LG FIX   040695103327
2004 HOL FL LG FIX
HOL 2004 SM FIX


but a trim....


  1  SELECT
  2    SUBSTR( x, 1, LENGTH( RTRIM( TRANSLATE( trim(x), '0123456789', '0000000000' ), '0' )
  3  ) )
  4* FROM t
ops$tkyte@ORA10GR2> /

SUBSTR(X,1,LENGTH(RTRIM(TRANSLATE(TRIM(X),'0123456
--------------------------------------------------
2003 HOL PERM FIX
2003 HOL STD LG FIX
2004 HOL FL LG FIX
HOL 2004 SM FIX



 

  1  select rtrim('2003 HOL PERM FIX         04069510367   ', '0123456789 ')
  2* from dual
SQL> /

RTRIM('2003HOLPER
-----------------
2003 HOL PERM FIX

Will trim all whitespace and any number from the right end of the string.

Unless the need is to remove a block of numbers at the end.
ex:HOL PERM FIX         04069510367 1235
results in HOL PERM FIX         04069510367

In which case something like this might work
  1  select rtrim(rtrim('2003 HOL PERM FIX         04069510367 1234', ' '), '0123456789')
  2* from dual
SQL> /

RTRIM(RTRIM('2003HOLPERMFIX04069510367
--------------------------------------
2003 HOL PERM FIX         04069510367

remove any trialing spaces and then trim the numbers 

Trims takes character sets - sweet. Much easier to read it that way.

Thanks Mark for pointing that out. One nitpick:
Your second (nested RTRIMs) statement could be simplified as:

>>1 select rtrim('2003 HOL PERM FIX 04069510367 1234','0123456789')from dual;

RTRIM('2003HOLPERMFIX040695103671234',
--------------------------------------
2003 HOL PERM FIX 04069510367

1 row selected.

Most of all thanks for the fast reply. I was amazed.
Thanks it is working fine.

select
regexp_replace(x,'[[:digit:]]+$','')
from t;

...or
regexp_replace(x,'\s+[[:digit:]]+$','')

to get rid of the spaces before the number

Mac,

I added the inner rtrim to remove any potential whitespace at the end but since the data was in a varchar2 any trialing whitespace would probably not exist.  Just like to cover any potential problems.  I guess there could be other non printable characters at the end also besides whitespace.

Just in case someone had something like this
SQL> create table msw0 (c1 varchar2(10))
  2  /

Table created.

SQL> insert into msw0 values('hello'||chr(32))
  2  /

1 row created.

SQL> select length(c1) from msw0
  2  /

LENGTH(C1)
----------
         6

  1  select c1||'*'
  2* from msw0
SQL> /

C1||'*'
-----------
hello *

 

read more of the thread.  Give that the data was in a char(50) column the inner rtrim is necessary.

SQL> create table msw0
  2  (c1 char(50))
  3  /

Table created.

SQL> insert into msw0 values('HOL PERM FIX         04069510367 1235
  2  
SQL> 
SQL> ed
Wrote file afiedt.buf

  1* insert into msw0 values('HOL PERM FIX         04069510367 1235')
SQL> /

1 row created.

SQL> select rtrim(c1, '0123456789')
  2  from msw0
  3  /

RTRIM(C1,'0123456789')
--------------------------------------------------
HOL PERM FIX         04069510367 1235

SQL> ed
Wrote file afiedt.buf

  1  select rtrim(rtrim(c1, ' '), '0123456789')
  2* from msw0
SQL> /

RTRIM(RTRIM(C1,''),'0123456789')
--------------------------------------------------
HOL PERM FIX         04069510367 

This was great!!!
I simply am amazed at how easy this ended up being.
Just knowing rtrim took away character sets is worth never forgetting. WOW!!

Hi,

I have this table.

select * from pcbq2;

YEAR MONTH_YEAR PROJ_NAME ERROR_TYPE
----- ---------- --------- ----------------------
2004 06-04 NEO Short/Open :
2004 06-04 NEO Drill table/data :
2004 06-04 NEO Milling table/data :
2004 06-04 NEO All Gerber file :

4 rows selected.

I want to remove ':' from error_type column. How can i do that? Just went tru all the reviews and finally came out with this.

select case when
replace( trim(translate( substr( error_type, instr(rtrim(error_type),'',-1)+1 ), ':','')), '0', '' ) is null
then substr( error_type, 1, instr(rtrim(error_type),'',-1) )
else error_type
end error_type
from pcbq2;

But unfortunately nothing came out. I think I misunderstood the concept.Can somebody please help me out?

Thanks for your help in advance.

yeah, i should think of that. You are totally awesome!!

Hi Tom,

Slightly different question (not trimming number but a string) -

I want to remove trailing pattern '_tas_d' from some strings.

In the below statements, why does the additional t vanish from 'account_tas_d' and 'agreement_tas_d':

SQL> select rtrim('address_name_tas_d', '_tas_d') from dual;

RTRIM('ADDRE
------------
address_name

SQL> select rtrim('account_tas_d', '_tas_d') from dual;

RTRIM(
------
accoun

SQL> select rtrim('agreement_tas_d', '_tas_d') from dual;

RTRIM('A
--------
agreemen

Thanks,
Naresh. 

rtrim looks at the SET of characters "_tas_d"

ops$tkyte@ORA10GR2> select rtrim('address_name_tas_d', '_tas_d'),
  2         rtrim('address_name_tas_d', 'd_sat' )
  3   from dual;

RTRIM('ADDRE RTRIM('ADDRE
------------ ------------
address_name address_name



if you want to remove the trailing STRING _tas_d, you might use:

case when COL like '%\_tas\_d' escape '\'
     then substr( COL, 1, length(col)-6)
     else COL
 end 

Thank You Tom, the "case" works.

I encountered the escape syntax for the first time - very useful to know this.