truncate leading zeros
Moderators: chulett, rschirm, roy
truncate leading zeros
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?
its showing as 000000000008194.000 (numaric(17,3)
it should be 8194.000.
Please suggest me how to convert?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Code: Select all
If col_name < 0 Then '-':Trim(Convert(' ','',(-1 * col_name) ),"0","L") Else Trim(Convert(' ','',(col_name) ),"0","L")
You are the creator of your destiny - Swami Vivekananda
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
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.
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
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
Code: Select all
svCol : Trim(Convert(' ','',(col_name) ),"0","L")
Left(svCol ,Index(svCol ,'.',1)+3)
You are the creator of your destiny - Swami Vivekananda
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)
You are the creator of your destiny - Swami Vivekananda
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
You are the creator of your destiny - Swami Vivekananda