• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

trigger for removing prepended zeros

 
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi al,
i need to write a trigger whicj will remove prepnding zeros froma values before insert or update
i need this on urgent basis
eg. 000123 should be entered as 123 in the amount colmun of payments table on insert as well as update
Can anyone help me with the trigger.
THIS IS AN SOS!!
thanx in advance
Chhaya
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What about just using to_number() function. Can any of your values have Alpha characters?
Mark
 
Ranch Hand
Posts: 254
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The NUMBER data type will ignore all leading zero. If you really have to use varchar, then you can(another solution is to use 'round' function) use 'fm' in the format mask to remove the leading zeros or spaces
For eg:
zerotest table with two columns (Name varchar2(10),amount varchar2(5))
insert into zerotest values('chhaya',to_char('00234','fm99999'));

Regards
Beksy
[ August 21, 2002: Message edited by: Beksy Kurian ]
 
Chhaya Dhanani
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanx both of you..
Chhaya
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic