nr V
asked on
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.
Function Main()
DTSDestination("CustomerID
DTSDestination("VATNr") = DTSSource("Col002")
Main = DTSTransformStat_OK
End Function
Exemple
CustomerID 0000052351 should be transformed to 52351.
and 0055522588 to 55522588.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm not that familiar with creating DTS packages, but it may work:)
Val(DTSSource("Col001"))
If you need to leave it as a string data type:
CStr(Val(DTSSource("Col001 ")))
If you need to leave it as a string data type:
CStr(Val(DTSSource("Col001
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 ",".
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 ",".
hmmm. I think AzraSound already got it...
ASKER
gencross,
LTrim(DTSSource("Col001"))
has no effect.
(Isn't LTrim for stripping spaces, isn't it?)
LTrim(DTSSource("Col001"))
has no effect.
(Isn't LTrim for stripping spaces, isn't it?)
ASKER
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.
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.
ASKER
Also the CustomerID is alphanumeric.
Exemple
CustomerID 0000052351 should be transformed to 52351,
0055522588 to 55522588 and 000A10101CC to A10101CC.
Exemple
CustomerID 0000052351 should be transformed to 52351,
0055522588 to 55522588 and 000A10101CC to A10101CC.
Val is not a VBScript function.
Anthony
Anthony
Use CLng instead (not sure if that has been suggested already)
Anthony
Anthony
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
Function Main()
DTSDestination("CustomerID
DTSDestination("VATNr") = CLng(DTSSource("Col002"))
Main = DTSTransformStat_OK
End Function
Sorry about the multiple posts.
Anthony
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("Col00 1")))
DTSDestination("VATNr") = CLng(DTSSource("Col002"))
Main = DTSTransformStat_OK
End Function
Function Main()
DTSDestination("CustomerID
DTSDestination("VATNr") = CLng(DTSSource("Col002"))
Main = DTSTransformStat_OK
End Function
LTrim is for spaces.....Sorry not thinking today, it's been a long one:)
ASKER
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.
CustomerID is alphanumeric.
Please see.
CustomerID 0000052351 should be transformed to 52351,
0055522588 to 55522588 and 000A10101CC to A10101CC.
ahh, that makes it more fun
Function Main()
DTSDestination("CustomerID ") = ConvertCustomerId(DTSSourc e("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
Function Main()
DTSDestination("CustomerID
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
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(DTSSour ce("Col001 "))
DTSDestination("VATNr") = CLng(DTSSource("Col002"))
Main = DTSTransformStat_OK
End Function
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
DTSDestination("VATNr") = CLng(DTSSource("Col002"))
Main = DTSTransformStat_OK
End Function
sorry, I have no idea why the CLng is on the line
DTSDestination("VATNr") = CLng(DTSSource("Col002"))
blasted cutting and pasting..
DTSDestination("VATNr") = CLng(DTSSource("Col002"))
blasted cutting and pasting..
NOTE: You do not need iPosition in the call. I forgot to remove it.
>>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("Col00 1")))
DTSDestination("VATNr") = CStr(CLng(DTSSource("Col00 2")))
Main = DTSTransformStat_OK
End Function
But is is not strictly necessary.
Anthony
CustomerID is alphanumeric.<<
VBScript uses variants only, so my code as previously posted will work
Function Main()
DTSDestination("CustomerID
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
DTSDestination("VATNr") = CStr(CLng(DTSSource("Col00
Main = DTSTransformStat_OK
End Function
But is is not strictly necessary.
Anthony
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,iStringLengt h,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
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,iStringLengt
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
nr V ~ have you had a chance to review some of the code suggestions since your last post?
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.Reg Exp")
RegEx.Pattern = "0*"
DTSDestination("CustomerID ") = RegEx.Replace(DTSSource("C ol001"), "")
DTSDestination("VATNr") = RegEx.Replace(DTSSource("C ol002"), "")
Set RegEx = Nothing
Main = DTSTransformStat_OK
End Function
Function Main()
Dim RegEx
Set RegEx = CreateObject("VBScript.Reg
RegEx.Pattern = "0*"
DTSDestination("CustomerID
DTSDestination("VATNr") = RegEx.Replace(DTSSource("C
Set RegEx = Nothing
Main = DTSTransformStat_OK
End Function
ASKER
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(DTSSour ce("Col001 "))
DTSDestination("VATNr") = RemoveLeadingZeros(DTSSour ce("Col002 "))
Main = DTSTransformStat_OK
End Function
Function RemoveLeadingZeros(SourceC olumn)
Do
If Left(SourceColumn, 1) = "0" Then SourceColumn = Right(SourceColumn, Len(SourceColumn) - 1)
Loop Until Left(SourceColumn, 1) <> "0"
RemoveLeadingZeros = SourceColumn
End Function
Thank you very much for you help. I've asked a moderator to do a pointsplit.
Function Main()
DTSDestination("CustomerID
DTSDestination("VATNr") = RemoveLeadingZeros(DTSSour
Main = DTSTransformStat_OK
End Function
Function RemoveLeadingZeros(SourceC
Do
If Left(SourceColumn, 1) = "0" Then SourceColumn = Right(SourceColumn, Len(SourceColumn) - 1)
Loop Until Left(SourceColumn, 1) <> "0"
RemoveLeadingZeros = SourceColumn
End Function
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
Computer101
E-E Moderator
Force accepted
** Mindphaser - Community Support Moderator **
AzraSound, rspahitz, acperkins, eebosscher, there will be a separate question with points for your help.
** Mindphaser - Community Support Moderator **
AzraSound, rspahitz, acperkins, eebosscher, there will be a separate question with points for your help.
Function Main()
DTSDestination("CustomerID
DTSDestination("VATNr") = DTSSource("Col002")
Main = DTSTransformStat_OK
End Function