truncate leading zeros

deva · Post by **deva** » Mon Apr 19, 2010 10:41 am

Hi I am loading data into a flat file. Data is comming in the folloing formate

its showing as 000000000008194.000 (numaric(17,3)

it should be 8194.000.

Please suggest me how to convert?

anbu · Post by **anbu** » Mon Apr 19, 2010 10:50 am

Use trim function to remove leading zeros and define field as varchar in target

deva · Post by **deva** » Mon Apr 19, 2010 12:28 pm

anbu wrote:Use trim function to remove leading zeros and define field as varchar in target

Hi it is working fine with the following syntax

Trim(Convert(' ','',(col_name) ),"0","L")

ray.wurlod · Post by **ray.wurlod** » Mon Apr 19, 2010 2:09 pm

Please mark thread as Resolved

deva · Post by **deva** » Tue Apr 20, 2010 8:27 am

what if we have negative values. how to truncate those. this logic is not working for the negative values.

example : -00000123.21
it should be : -123.21

anbu · Post by **anbu** » Tue Apr 20, 2010 8:43 am

Code: Select all

If col_name < 0 Then '-':Trim(Convert(' ','',(-1 * col_name) ),"0","L") Else Trim(Convert(' ','',(col_name) ),"0","L")

priyadarshikunal · Post by **priyadarshikunal** » Tue Apr 20, 2010 8:59 am

what about DecimalToString() with rtype as suppress_zero?

deva · Post by **deva** » Tue Apr 20, 2010 9:26 am

priyadarshikunal wrote:what about DecimalToString() with rtype as suppress_zero?

In a column I have both negative and possitive vlues. I have to eliminate the leading zeros.

as of now I am using the below statement

If (IsNull(Col_name)=1) Then '0':Trim(Convert(' ','',(col_name) ),"0","L") Else Trim(Convert(' ','',(col_name) ),"0","L")

The above statement is working fine for possitive values. But it is not working for negative values. Can I have a statment which will work for both positive and negative values.

deva · Post by **deva** » Tue Apr 20, 2010 10:16 am

If I use the below statement leading and tailring zeros are elimination but I need 3 numbers after decimal. my input is numaric and I changed my target as varchar.

If (IsNull(col_name)=1) Then '0':Trim(Convert(' ','',(lnk_lkp_IPS_UOM2.EXTND_SALES_AMT) ),"0","B") Else if col_name < 0 Then '-':Trim(Convert(' ','',(-1 * col_name) ),"0","B") Else Trim(Convert(' ','',(col_name) ),"0","B")
example

0000231.230000 ==> 231.230
-0000231.2000000 ==> -231.200
-0000034.00000 ==> -34.000

anbu · Post by **anbu** » Tue Apr 20, 2010 10:33 am

Code: Select all

svCol : Trim(Convert(' ','',(col_name) ),"0","L")
Left(svCol ,Index(svCol ,'.',1)+3)

deva · Post by **deva** » Tue Apr 20, 2010 11:14 am

its not working , Can you please modify the above statement what I given

anbu · Post by **anbu** » Tue Apr 20, 2010 12:35 pm

Code: Select all

svCol : If (IsNull(Col_name)=1) Then '0':Trim(Convert(' ','',(col_name) ),"0","L") Else if col_name < 0 Then '-':Trim(Convert(' ','',(-1 * col_name) ),"0","L") Else Trim(Convert(' ','',(col_name) ),"0","L")

target_col : Left(svCol ,Index(svCol ,'.',1)+3)

deva · Post by **deva** » Tue Apr 20, 2010 3:13 pm

Thanks for your reply,
It is working fine. But I found one more issue which the above code is not supporting

example :

input : 0000000.123
output should be 0.123

but if I apply the above code I am getting '.123'. I need '0' before decimal point.

Thanks for your support

anbu · Post by **anbu** » Tue Apr 20, 2010 3:25 pm

Code: Select all

svCol : If (IsNull(Col_name)=1) Then '0':Trim(Convert(' ','',(col_name) ),"0","L") Else if col_name < 0 Then '-':Trim(Convert(' ','',(-1 * col_name) ),"0","L") Else Trim(Convert(' ','',(col_name) ),"0","L") 

svCol1 : If svCol[1,1] = '.' Then '0' : svCol Else svCol

target_col : Left(svCol ,Index(svCol1 ,'.',1)+3