Wednesday, March 21, 2012

Help convert MS Access function to MS SQL User Defined Function

I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.
For example:
TrimZero("000000001023") > "1023"
TrimZero("E1025") > "E1025"
TrimZero("000000021021") > "21021"
TrimZero("R5545") > "R5545"
Here is the function that works in access:
Public Function TrimZero(strField As Variant) As String
Dim strReturn As String
If IsNull(strField) = True Then
strReturn = ""
Else
strReturn = strField
Do While Left(strReturn, 1) = "0"
strReturn = Mid(strReturn, 2)
Loop
End If
TrimZero = strReturn
End Function
Is this not possible? I seem not to be able to figure it out onmy own. If I am missing any information in my question, whichinformation should I include?
|||Okay.. with the help from experts-exchange.com I was able to get the answer.
CREATE FUNCTION [dbo].[TrimZero] (@.MyString varchar(50))
RETURNS varchar(50)
AS
BEGIN
If ISNUMERIC(@.MyString) = 1
Begin
While Left (@.MyString,1) = '0'
Begin
Set @.MyString = Right(@.Mystring,Len(@.Mystring) -1)
End
End
Return @.MyString
END
|||A even better function to do the same thing:
create function dbo.trimzero (@.mystring varchar(50))
returns varchar(50)
AS
begin
declare @.myStrInt int
if isnumeric(@.mystring)=1
set @.myString = cast(@.mystring as int)
return @.mystring
end
GO

No comments:

Post a Comment