Win a copy of Escape Velocity: Better Metrics for Agile Teams this week in the Agile and Other Processes forum!
  • 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
  • Liutauras Vilda
  • Tim Cooke
  • Paul Clapham
  • Jeanne Boyarsky
Sheriffs:
  • Ron McLeod
  • Frank Carver
  • Junilu Lacar
Saloon Keepers:
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Al Hobbs
  • Carey Brown
Bartenders:
  • Piet Souris
  • Frits Walraven
  • fred rosenberger

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
 
Mo-om! You're embarassing me! Can you just read a tiny ad like a normal person?
the value of filler advertising in 2021
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic