Unable to trim leading zeroes using trim()

[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)))

[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

[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.

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

[kumar_s]

Hi,

Try with the paramteres passed for the Trim function in single quotes.

Code: Select all

Trim(link.field,'0','L')

-Kumar

[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.

Hi,

Try with the paramteres passed for the Trim function in single quotes.

Code: Select all

Trim(link.field,'0','L')

-Kumar

[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.

[chulett]

It's not an integer, but rather an alphanumeric value: "000987A87".

[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...

[jmessiha]

It's an alphanumeric (VARCHAR). 0009321A3 is a valid input, but I'd need the output to be 9321A3

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.

[jmessiha]

I don't want to eliminate every 0. Only leading 0's. Also, I think ereplace is server only. Right?

er

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...

[jmessiha]

Right.

It's not an integer, but rather an alphanumeric value: "000987A87".

[kommven]

Did you Use

Code: Select all

Abs(Input.Col1)

and let us know,

-R

[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

[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.

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

[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