UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

 
   Reply to this topicStart new topic
> trim leading zeros from a text field in select statement    
 
   
estel_mm
post Sep 10 2010, 11:45 AM
Post#1



Posts: 47
Joined: 25-August 10



Hello everyone,
I have a text field of varying length which has leading zeros.
How can I remove these zeros in my select statement?
THere is an example of my data:
CODE
000000000000996918 1
000000000007104466 1
Go to the top of the page
 
theDBguy
post Sep 10 2010, 12:00 PM
Post#2


Access Wiki and Forums Moderator
Posts: 61,558
Joined: 19-June 07
From: SoCal, USA


Hi,
And do you want to keep the space and the trailing number?
Go to the top of the page
 
estel_mm
post Sep 10 2010, 12:47 PM
Post#3



Posts: 47
Joined: 25-August 10



Trailing number yes. Space, doesn't really matter.
*EDIT***
It's actually probably best that I keep the end of the string the same way. I would just need to remove the leading zeros at the front. I tried using TRIM or SUBSTRING or CAST but to no avail...
Go to the top of the page
 
theDBguy
post Sep 10 2010, 05:14 PM
Post#4


Access Wiki and Forums Moderator
Posts: 61,558
Joined: 19-June 07
From: SoCal, USA


Hi,
Thanks for the additional info. I'm afraid I am not aware of any function that will do that. You may have to roll out your own custom function.
Oimagine you would do something like this (in pseudocode):
1. Loop through the field value one character at a time.
2. Check if the value is equal to zero.
3. Increment a counter variable to indicate how many zeroes you find.
4. After all the leading zeroes are found, the counter should contain the position number of the last zero.
5. You can now use the Mid() function to return the truncated value of the field without the leading zeroes.
Hope that helps...
Go to the top of the page
 
theDBguy
post Sep 11 2010, 12:36 PM
Post#5


Access Wiki and Forums Moderator
Posts: 61,558
Joined: 19-June 07
From: SoCal, USA


Hi,
Thanks to a hint from another UA member (Walter Niesz), I was able to put together the function below:
CODE
Public Function TrimZero(str As String) As String
If Left(str, 1) = "0" Then
    TrimZero = TrimZero(Mid(str, 2))
Else
    TrimZero = str
End If
    
End Function

Hope that helps...
Go to the top of the page
 
estel_mm
post Sep 13 2010, 08:53 AM
Post#6



Posts: 47
Joined: 25-August 10



Thanks DBguy.
I am trying to do this from an actual query though. Is there any way to do it from a Select statement? Could I call the function you provided in my select statement?
Go to the top of the page
 
estel_mm
post Sep 13 2010, 11:12 AM
Post#7



Posts: 47
Joined: 25-August 10



I actually found another thread similar to mine with a solution.
The CLng(fieldName) seems to work fine in trimming leading zeros.
Go to the top of the page
 
John Spencer
post Sep 13 2010, 11:39 AM
Post#8


UtterAccess VIP
Posts: 2,550
Joined: 24-March 08
From: Columbia, Maryland


CLng will work to trim leading zeroes as long as the number value does not exceed the maximum permissible value for a Long integer and there is NO non-numeric characters such as a space.
Lng("000000000000996918 1") will error
CLng("000000000000996918") will return 996918 (note the dropped space + 1)
CLng("800000000000996918") will error
Val("000000000000996918 1") will return 996918 (note the dropped space + 1)
Clng will fail with any number greater than 2147483647
000000002147483647 is the maximum value you could "convert" with CLng
Go to the top of the page
 
estel_mm
post Sep 13 2010, 12:09 PM
Post#9



Posts: 47
Joined: 25-August 10



John, thanks for your help once again.
may have spaces in the string so I will have to use Val(). I don't think the number will exceed 2147483647.
Go to the top of the page
 
theDBguy
post Sep 13 2010, 11:50 PM
Post#10


Access Wiki and Forums Moderator
Posts: 61,558
Joined: 19-June 07
From: SoCal, USA


Hi,
Sorry for the delay. Yes, you can call the above function in a query.
Odidn't tell you about the Val() function because I thought you said you wanted to preserve the space and trailing characters/digits.
Good luck!
Go to the top of the page
 
estel_mm
post Sep 20 2010, 02:26 PM
Post#11



Posts: 47
Joined: 25-August 10



Not a problem. Works perfectly.
Go to the top of the page
 
theDBguy
post Sep 20 2010, 02:33 PM
Post#12


Access Wiki and Forums Moderator
Posts: 61,558
Joined: 19-June 07
From: SoCal, USA


Hi,
Glad to hear that. I thought others might be able to use a function like that too, so I posted the code in the Function Library.
Thanks to your question (and through encouragements from the other experts here), I was able to write that function and share it with others.
Good luck with your project.
Go to the top of the page
 


RSSSearch   Top   Lo-Fi    31st March 2015 - 01:05 PM