7/1/13

How to search only for numbers in a column in Oracle : rtrim

 with test as
(select '1gnec16z15j129947' vin from dual union all
 SELECT '99999999999999999' vin FROM DUAL UNION ALL
 SELECT '00000000000000000' vin FROM DUAL UNION ALL
 SELECT '11111111111111111' vin FROM DUAL UNION ALL
  select 'abcdefghijklmnop' vin from dual union all
 select 'jtmbk31v076017323' vin from dual
 )
 SELECT  * FROM TEST WHERE not rtrim(vin, '0123456789') is null


OUTPUT :

below is the output of the above query which is eliminating the numbers:

1gnec16z15j129947
abcdefghijklmnop
jtmbk31v076017323

*************************************************

 with test as
(select '1gnec16z15j129947' vin from dual union all
 SELECT '99999999999999999' vin FROM DUAL UNION ALL
 SELECT '00000000000000000' vin FROM DUAL UNION ALL
 SELECT '11111111111111111' vin FROM DUAL UNION ALL
  select 'abcdefghijklmnop' vin from dual union all
 select 'jtmbk31v076017323' vin from dual
 )
 SELECT  * FROM TEST WHERE rtrim(vin, '0123456789') is null


OUTPUT :

below is the output of the above query which is eliminating rows other than numbers.

99999999999999999
00000000000000000
11111111111111111


0 comments:

Post a Comment