Unable to trim leading zeroes using trim()
Moderators: chulett, rschirm, roy
Unable to trim leading zeroes using trim()
Using the trim function to remove leading zeroes does not appear to be working.
The input value was 00092102A92 and I wanted it to be 92102A92 on the output. The number of leading zeroes can very and as you can see, there are alphas in my varchar field.
I tried to use trim(DSLink123.MY_FIELD,"0","L") to no avail. Even using a literal did not work.
Trim("00009", "0", "L") returned 00009.
What am I doing wrong?
I ended up using the following but would like to know if I can change this to a trim:
convert(' ','0',trimf(convert('0',' ',DSLink123.FIELD_NAME)))
The input value was 00092102A92 and I wanted it to be 92102A92 on the output. The number of leading zeroes can very and as you can see, there are alphas in my varchar field.
I tried to use trim(DSLink123.MY_FIELD,"0","L") to no avail. Even using a literal did not work.
Trim("00009", "0", "L") returned 00009.
What am I doing wrong?
I ended up using the following but would like to know if I can change this to a trim:
convert(' ','0',trimf(convert('0',' ',DSLink123.FIELD_NAME)))
Hi
The trim function must work. I had used it so many jobs and never had this problem. I used the below to do a quick test to see what the result is.
trim('000987A87',"0","L")
Result = 987A87
It works...Check again or use the above code and put in the transformer and test
The trim function must work. I had used it so many jobs and never had this problem. I used the below to do a quick test to see what the result is.
trim('000987A87',"0","L")
Result = 987A87
It works...Check again or use the above code and put in the transformer and test
Regards
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
No, again, that is not working. I get 000987A87 when I copy and paste your code into my job. Are you using a parallel job? I am.
rasi wrote:Hi
The trim function must work. I had used it so many jobs and never had this problem. I used the below to do a quick test to see what the result is.
trim('000987A87',"0","L")
Result = 987A87
It works...Check again or use the above code and put in the transformer and test
Hi,
Try with the paramteres passed for the Trim function in single quotes.
-Kumar
Try with the paramteres passed for the Trim function in single quotes.
Code: Select all
Trim(link.field,'0','L')
I get the same result whether I use single, double, mixed and every combination of quotes that I could think of. This is quite frustrating.
kumar_s wrote:Hi,
Try with the paramteres passed for the Trim function in single quotes.
-KumarCode: Select all
Trim(link.field,'0','L')
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
What is the data type of MYFIELD? Have you tried converting it to integer (say in a Transformer stage)? That should remove the zeroes - if it doesn't adding zero to the integer definitely will.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Charter Member
- Posts: 38
- Joined: Wed Mar 17, 2004 1:16 am
- Location: USA
Re: Unable to trim leading zeroes using trim()
if u get an alphanumeric value u shld be able to use the 'ereplace' function and eliminate the "0" char in ur string...but i think the trim shld work...
..i did not invent many things..but i can make them Better..
It's an alphanumeric (VARCHAR). 0009321A3 is a valid input, but I'd need the output to be 9321A3
ray.wurlod wrote:What is the data type of MYFIELD? Have you tried converting it to integer (say in a Transformer stage)? That should remove the zeroes - if it doesn't adding zero to the integer definitely will.
Re: Unable to trim leading zeroes using trim()
I don't want to eliminate every 0. Only leading 0's. Also, I think ereplace is server only. Right?
er
er
srikanthd1978 wrote:if u get an alphanumeric value u shld be able to use the 'ereplace' function and eliminate the "0" char in ur string...but i think the trim shld work...
Last edited by kommven on Fri Feb 10, 2006 12:35 am, edited 1 time in total.
Guys
trim('000987A87',"0","L")
Result = 987A87
has to work with Trim function. Unless there is something missing. Sometimes Number 0 is type as Alphabet O which is very hard to debug. Check is it something like that. Try creating a new simple job with this. Sometimes it does helps in debugging....
regards
trim('000987A87',"0","L")
Result = 987A87
has to work with Trim function. Unless there is something missing. Sometimes Number 0 is type as Alphabet O which is very hard to debug. Check is it something like that. Try creating a new simple job with this. Sometimes it does helps in debugging....
regards
Regards
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
No, it's not working. I copied and pasted your code in there, so there is no chance of typing a zero as an O or vice-versa. If it works in yours, it should work in mine, right? Well it's not.
rasi wrote:Guys
trim('000987A87',"0","L")
Result = 987A87
has to work with Trim function. Unless there is something missing. Sometimes Number 0 is type as Alphabet O which is very hard to debug. Check is it something like that. Try creating a new simple job with this. Sometimes it does helps in debugging....
regards