Strip leading zeroes:

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.
nr VAsked:
Who is Participating?
 
gencrossConnect With a Mentor Commented:
whoops!  I mean this...

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

0
 
gencrossCommented:
What about this?

Function Main()
    DTSDestination("CustomerID") = LTrim(DTSSource)("Col001")
    DTSDestination("VATNr") = DTSSource("Col002")
    Main = DTSTransformStat_OK
End Function
0
 
gencrossCommented:
I'm not that familiar with creating DTS packages, but it may work:)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
AzraSoundCommented:
Val(DTSSource("Col001"))

If you need to leave it as a string data type:
CStr(Val(DTSSource("Col001")))
0
 
rspahitzCommented:
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
 
rspahitzCommented:
hmmm. I think AzraSound already got it...
0
 
nr VAuthor Commented:
gencross,
LTrim(DTSSource("Col001"))
has no effect.
(Isn't LTrim for stripping spaces, isn't it?)
0
 
nr VAuthor Commented:
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
 
nr VAuthor Commented:
Also the CustomerID is alphanumeric.

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

0
 
Anthony PerkinsCommented:
Val is not a VBScript function.

Anthony
0
 
Anthony PerkinsCommented:
Use CLng instead (not sure if that has been suggested already)

Anthony
0
 
Anthony PerkinsCommented:
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
 
ebosscherCommented:
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
 
gencrossCommented:
LTrim is for spaces.....Sorry not thinking today, it's been a long one:)
0
 
nr VAuthor Commented:
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
 
ebosscherCommented:
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
 
gencrossCommented:
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
 
ebosscherCommented:
sorry, I have no idea why the CLng is on the line

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

blasted cutting and pasting..
0
 
gencrossCommented:
NOTE: You do not need iPosition in the call.  I forgot to remove it.
0
 
Anthony PerkinsCommented:
>>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
 
rspahitzCommented:
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
 
ebosscherCommented:
nr V ~ have you had a chance to review some of the code suggestions since your last post?
0
 
Anthony PerkinsCommented:
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
 
nr VAuthor Commented:
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
 
Computer101Commented:
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
 
MindphaserCommented:
Force accepted

** Mindphaser - Community Support Moderator **

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

From novice to tech pro — start learning today.