Improve company productivity with a Business Account.Sign Up

x
Solved

Strip leading zeroes:

Posted on 2002-07-10
Medium Priority
1,022 Views
Last Modified: 2007-12-19
I need something to strip leading zeroes in a visual basic transformation script in a Sql Server dts package.

Function Main()
DTSDestination("CustomerID") = DTSSource("Col001")
DTSDestination("VATNr") = DTSSource("Col002")
Main = DTSTransformStat_OK
End Function

Exemple
CustomerID 0000052351 should be transformed to 52351.
and 0055522588 to 55522588.
0
Question by:nr V
• 6
• 5
• 5
• +5
26 Comments

LVL 4

Expert Comment

ID: 7144148
What about this?

Function Main()
DTSDestination("CustomerID") = LTrim(DTSSource)("Col001")
DTSDestination("VATNr") = DTSSource("Col002")
Main = DTSTransformStat_OK
End Function
0

LVL 4

Accepted Solution

gencross earned 320 total points
ID: 7144151
whoops!  I mean this...

Function Main()
DTSDestination("CustomerID") = LTrim(DTSSource("Col001"))
DTSDestination("VATNr") = DTSSource("Col002")
Main = DTSTransformStat_OK
End Function

0

LVL 4

Expert Comment

ID: 7144161
I'm not that familiar with creating DTS packages, but it may work:)
0

LVL 28

Expert Comment

ID: 7144171
Val(DTSSource("Col001"))

If you need to leave it as a string data type:
CStr(Val(DTSSource("Col001")))
0

LVL 22

Expert Comment

ID: 7144202
val(0000052351) = 52351

This assumes that the item passed into the VAL statement is either a string of digits, variant of all digits, or numeric.

But val("Col001") is zero because the val function stops when it finds a non-numeric character.  (Note that "." is considered numeric, as is "E" and "D" in some cases: val(3.5E3)=3500, and sometimes "," is valid such as val("35,17,20")=35)

Also, it will not work with foreign decimal symbols such as ",".

0

LVL 22

Expert Comment

ID: 7144206
hmmm. I think AzraSound already got it...
0

Author Comment

ID: 7144213
gencross,
LTrim(DTSSource("Col001"))
has no effect.
(Isn't LTrim for stripping spaces, isn't it?)
0

Author Comment

ID: 7144225
Test of Val(DTSSource("Col001")). Import failed.
I think that Val is not a valid function.

The DTS Designer is using VBScript and I can't see it in the list of functions.
0

Author Comment

ID: 7144235
Also the CustomerID is alphanumeric.

Exemple
CustomerID 0000052351 should be transformed to 52351,
0055522588 to 55522588 and 000A10101CC to A10101CC.

0

LVL 75

Expert Comment

ID: 7144274
Val is not a VBScript function.

Anthony
0

LVL 75

Expert Comment

ID: 7144285
Use CLng instead (not sure if that has been suggested already)

Anthony
0

LVL 75

Expert Comment

ID: 7144290
In other words, your DTS script should look like this:
Function Main()
DTSDestination("CustomerID") = CLng(DTSSource("Col001"))
DTSDestination("VATNr") = CLng(DTSSource("Col002"))
Main = DTSTransformStat_OK
End Function

Sorry about the multiple posts.

Anthony
0

LVL 6

Expert Comment

ID: 7144301
actually the CLng might not work, because you are trying to save a string?  Azrasound already gave it, just with an obsolete function name (that does the same thing)

Function Main()
DTSDestination("CustomerID") = CStr(CLng(DTSSource("Col001")))
DTSDestination("VATNr") = CLng(DTSSource("Col002"))
Main = DTSTransformStat_OK
End Function
0

LVL 4

Expert Comment

ID: 7144325
LTrim is for spaces.....Sorry not thinking today, it's been a long one:)
0

Author Comment

ID: 7144327
CLng is converting to a number isn't it?
CustomerID is alphanumeric.

Please see.

CustomerID 0000052351 should be transformed to 52351,
0055522588 to 55522588 and 000A10101CC to A10101CC.

0

LVL 6

Expert Comment

ID: 7144340
ahh, that makes it more fun

Function Main()
DTSDestination("CustomerID") = ConvertCustomerId(DTSSource("Col001"))
DTSDestination("VATNr") = CLng(DTSSource("Col002"))
Main = DTSTransformStat_OK
End Function

Function ConvertCustomerId(szCustId)
Dim dwLoop

For dwLoop = 1 to Len(szCustId)
If Mid(szCustId, dwLoop, 1) <> "0" Then
Exit For
End If
Next

ConvertCustomerId = Mid(szCustId, dwLoop)
End Function
0

LVL 4

Expert Comment

ID: 7144349
Here is a little function I wrote to remove leading zeros.

Public Function RemoveLeadingZeros(sText As String) As String

Dim iPosition As Integer

Do While Left(sText, 1) = 0
sText = Mid(sText, 2)
Loop

RemoveLeadingZeros = sText

End Function

Your call would look like this...

Function Main()
DTSDestination("CustomerID") = RemoveLeadingZeros(DTSSource("Col001"))
DTSDestination("VATNr") = CLng(DTSSource("Col002"))
Main = DTSTransformStat_OK
End Function
0

LVL 6

Expert Comment

ID: 7144350
sorry, I have no idea why the CLng is on the line

DTSDestination("VATNr") = CLng(DTSSource("Col002"))

blasted cutting and pasting..
0

LVL 4

Expert Comment

ID: 7144353
NOTE: You do not need iPosition in the call.  I forgot to remove it.
0

LVL 75

Expert Comment

ID: 7144363
>>CLng is converting to a number isn't it?
CustomerID is alphanumeric.<<

VBScript uses variants only, so my code as previously posted will work
Function Main()
DTSDestination("CustomerID") = CLng(DTSSource("Col001"))
DTSDestination("VATNr") = CLng(DTSSource("Col002"))
Main = DTSTransformStat_OK
End Function

However if you prefer, than by all means add the CStr function as  follows:

Function Main()
DTSDestination("CustomerID") = CStr(CLng(DTSSource("Col001")))
DTSDestination("VATNr") = CStr(CLng(DTSSource("Col002")))
Main = DTSTransformStat_OK
End Function

But is is not strictly necessary.

Anthony
0

LVL 22

Expert Comment

ID: 7144641
So let me get this straight by putting things...

Starting string          Required String
CustomerID 0000052351    52351
0055522588               55522588
000A10101CC              A10101CC

If the above is correct then you have a problem because the first item does not start with leading zeros.  This means that you are starting with something that may or may not have leading zeros and will remove any subsequent zeroes that are followed by any digits.

I'd do it this way:

' First search for the "leading" zero
'INPUT = IDString

dim bLeadingZeroFound as boolean
dim iStringLength as integer
dim strNextChar as string * 1
dim iNonZeroPosition as integer

bLeadingZeroFound = false
for iStringLength = 1 to len(IDString)
strNextChar = mid\$(IDString,iStringLength,1)
if strNextChar = "0" then
' This is a leading zero
iNonZeroPosition = iStringLength
bLeadingZeroFound = true
exit for
elseif strNextChar > "0" and strNextChar <= "9" then
' A non-zero digit found, so there are no leading zeros
exit for
endif
next iStringLength

if bLeadingZeroFound then
' A leading zero exists, so find the next non-zero
while mid\$(IDString, iNonZeroPosition, 1) = "0" and iNonZeroPosition < len(IDString)
' still a zero, so move to next position
iNonZeroPosition = iNonZeroPosition + 1
wend
' now trim off all the leading stuff
NewIDString = mid\$(IDString, iNonZeroPosition)
else
NewIDString = IDString
endif

debug.print "Old string="; IDString, "Trimmed="; NewIDString

0

LVL 6

Expert Comment

ID: 7146152
nr V ~ have you had a chance to review some of the code suggestions since your last post?
0

LVL 75

Expert Comment

ID: 7146845
Or if you want a VBScript solution for DTS to remove leading zeros from alpha-numeric strings than:

Function Main()
Dim RegEx

Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "0*"
DTSDestination("CustomerID") = RegEx.Replace(DTSSource("Col001"), "")
DTSDestination("VATNr") = RegEx.Replace(DTSSource("Col002"), "")
Set RegEx = Nothing
Main = DTSTransformStat_OK
End Function
0

Author Comment

ID: 7152280
I used this solution. Works fine.

Thank you very much for you help. I've asked a moderator to do a pointsplit.

Function Main()
DTSDestination("CustomerID") = RemoveLeadingZeros(DTSSource("Col001"))
DTSDestination("VATNr") = RemoveLeadingZeros(DTSSource("Col002"))
Main = DTSTransformStat_OK
End Function

Function RemoveLeadingZeros(SourceColumn)
Do
If Left(SourceColumn, 1) = "0" Then SourceColumn = Right(SourceColumn, Len(SourceColumn) - 1)
Loop Until Left(SourceColumn, 1) <> "0"
RemoveLeadingZeros = SourceColumn
End Function
0

LVL 1

Expert Comment

ID: 7152307
Points reduced for a split.  You can now accept one experts comment as an answer (gencross).  After that, make another question in this topic area for the other experts (AzraSound, rspahitz, acperkins, eebosscher).  Make the question for the amount of points intended (80).  Label the question, "Question for (Expert Name) and post this question number in the base of the question, i.e. For your assistance in question # --------

Computer101
E-E Moderator
0

LVL 6

Expert Comment

ID: 7177828
Force accepted

** Mindphaser - Community Support Moderator **

AzraSound, rspahitz, acperkins, eebosscher, there will be a separate question with points for your help.
0

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment Already a member? Login.

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month7 days, 15 hours left to enroll

585 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.