Page 1 of 2
Unable to trim leading zeroes using trim()
Posted: Wed Feb 08, 2006 1:53 pm
by jmessiha
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)))
Posted: Wed Feb 08, 2006 6:36 pm
by rasi
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
Posted: Wed Feb 08, 2006 10:15 pm
by jmessiha
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
Posted: Wed Feb 08, 2006 10:22 pm
by kumar_s
Hi,
Try with the paramteres passed for the Trim function in single quotes.
-Kumar
Posted: Wed Feb 08, 2006 10:28 pm
by jmessiha
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.
-Kumar
Posted: Wed Feb 08, 2006 10:52 pm
by ray.wurlod
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.
Posted: Wed Feb 08, 2006 11:53 pm
by chulett
It's not an integer, but rather an alphanumeric value: "000987A87".
Re: Unable to trim leading zeroes using trim()
Posted: Thu Feb 09, 2006 12:06 am
by srikanthd1978
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...
Posted: Thu Feb 09, 2006 2:17 am
by jmessiha
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()
Posted: Thu Feb 09, 2006 2:19 am
by jmessiha
I don't want to eliminate every 0. Only leading 0's. Also, I think ereplace is server only. Right?
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...
Posted: Thu Feb 09, 2006 2:20 am
by jmessiha
Right.
chulett wrote:It's not an integer, but rather an alphanumeric value: "000987A87".
Posted: Thu Feb 09, 2006 6:43 pm
by kommven
Did you Use
and let us know,
-R
Posted: Thu Feb 09, 2006 7:05 pm
by rasi
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
Posted: Fri Feb 10, 2006 12:26 am
by jmessiha
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
Posted: Fri Feb 10, 2006 7:32 am
by kumar_s
Is there any thing else funny things going on in your project.
Just give a shot to restart your server and try again.
-Kumar