Creating/Using Functions in SQL Server

Background:
I am relatively new to SQL Server and currently writing a data conversion, reading in clients legacy data and converting the formats to the new SQL back end DB.  I wrote and tested the conversion in MS Access 2013 using a subset of the clients data.  It worked perfectly but now that I'm running the conversion with the full set of client data it's taking many hours to process.
With the help of EE I was able to convert some of my existing Access conversion logic to run completely in a SQL Server SPROC.  This works great for instances where I'm just moving data from the old input field to a new one.  It cut processing times from several hours to minutes.
However, some of my conversion routines have user written functions, used when moving the data to the new SQL table.  I'd like to convert these to run purely in SQL but have zero experience using SQL Server function.

Question:
These are the functions as they exist in MS Access.  These examples are pureley string manipulation.  If I can convert all of these to SQL Functions I can move the conversion process of one more input file totally to the much faster SQL Server side.  If anyone can show what these would look like as SQL Server functions it would be greatly appreciated.

'removeFunkyCharacters'  looks for certain characters in a passed string.  Any time one of the characters is found, it is replaced with a space.

Public Function removeFunkyCharacters(passedString As String) As String
'
removeFunkyCharacters = ""
'
Dim wkString As String

wkString = passedString
wkString = Replace(wkString, vbCrLf, Space(1))
wkString = Replace(wkString, vbCr, Space(1))
wkString = Replace(wkString, Chr(34), Space(1))     ' double quote
wkString = Replace(wkString, Chr(35), Space(1))     ' # Pound sign
wkString = Replace(wkString, Chr(39), Space(1))     ' single quote
wkString = Replace(wkString, "*", Space(1))
wkString = Replace(wkString, "(", Space(1))
wkString = Replace(wkString, ")", Space(1))
wkString = Replace(wkString, "\", Space(1))
wkString = Replace(wkString, "@", Space(1))
'
removeFunkyCharacters = Trim(wkString)

End Function

Open in new window


cleanupAddrLines:  This one looks for certain characters in position 29 of the passed string.  If the characters are found, they are effectively eliminated by concatenating the part starting in position 30 , starting in position 29.

Public Function cleanupAddrLines(passedAddrLine As String) As String
'
Dim wkAddr As String
Dim wkAddrLength As Long
'
wkAddr = Trim(removeFunkyCharacters(passedAddrLine))
'
'     '*' in position 30 (unmailable) or position 1 (addr check needed) is removed in 'removefunkycharaters' just above
'
wkAddrLength = Len(wkAddr)
'
If wkAddrLength > 29 Then
    If Mid(wkAddr, 30, 1) = "*" Or Mid(wkAddr, 30, 1) = "b" Then
        wkAddr = Mid(wkAddr, 1, 29)
        If wkAddrLength > 30 Then
            wkAddr = wkAddr & Mid(wkAddr, 31)
        End If
    End If
End If
'
cleanupAddrLines = wkAddr
'
End Function

Open in new window


parseCityStateZip:  Parses out City State and Zip from a passed string.  Some very oddball rules evolved based on some very oddball scenarios in the clients legacy data.

Public Sub parseCityStateZip(passedCityStZip As Variant, _
                             returnCity As String, _
                             returnState As String, _
                             returnZip As String)
'
returnCity = ""
returnState = ""
returnZip = ""
'
Dim wkStrLength As Long
Dim wkCityStZip As String
wkCityStZip = Trim(removeFunkyCharacters(Nz(passedCityStZip, "")))

wkStrLength = Len(wkCityStZip)

If wkStrLength = 0 Then
    Exit Sub
End If
'
Dim wkstrPosition As Long
Dim wkStr As String
Dim wkZipBeginPos As Long
Dim wkStateBeginPos As Long

Dim i As Long
'
Dim wkIsANumber As Boolean
Dim wkIsALetter As Boolean
'
' work from the end and pick zip first
'
wkIsANumber = True
i = wkStrLength + 1
wkZipBeginPos = i
'
While wkIsANumber
    i = i - 1
    If i < 1 Then
        wkIsANumber = False
    Else
        wkStr = Mid(wkCityStZip, i, 1)
        wkIsANumber = isANumber(wkStr)
        If wkIsANumber Then
            returnZip = wkStr & returnZip
            wkZipBeginPos = i
        End If
    End If
Wend
returnZip = Mid(Trim(returnZip), 1, 10)
'
' zip should be loaded above, now get state, 2 letters in front of zip
'
Dim wkStateLetterCnt As Long
wkStateLetterCnt = 0
'
wkIsALetter = True
i = wkZipBeginPos
wkStateBeginPos = i
'
While wkIsALetter
    i = i - 1
    If i < 1 Then
        wkIsALetter = False
    Else
        wkStr = Mid(wkCityStZip, i, 1)
        If wkStr = Space(1) Or wkStr = "," Then             'bypass spaces moving back
        Else
            wkIsALetter = isALetter(wkStr)
            If wkIsALetter Then
                returnState = wkStr & returnState
                wkStateBeginPos = i
                wkStateLetterCnt = wkStateLetterCnt + 1
                If wkStateLetterCnt > 1 Then   ' only two letters in the state
                    wkIsALetter = False
                End If
            End If
        End If
    End If
Wend
returnState = Mid(Trim(returnState), 1, 2)
'
' Get city, starting at 1 all the way to the beginning of state
'
For i = 1 To wkStateBeginPos - 1
    wkStr = Mid(wkCityStZip, i, 1)
    If wkStr = "," Then
        Exit For
    End If
    returnCity = returnCity & wkStr
Next i
'
returnCity = Mid(Trim(returnCity), 1, 30)
'
End Sub

Open in new window

LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
If you are doing this as pass through query, I would simply recommend that you call these functions in the query you are using to build the pass-through query.

You could write SQL Server stored procedures or UserDefinedFunctions, but if this is a one-time thing, just for the transition from Access to SQL Server, then I'd probably just do it in the query (recordset) used to build your pass-through query.

Dale
mlcktmguyAuthor Commented:
Thank you Dale.  Yes, this will be a one time thing, once I get it running in under 24 hours.  
This is not being done as a pass thru query but if that's faster I would certainly be open to it, especially if I wouldn't have to re-create the functions.  I have other data sets being converted.  I wasn't looking forward to re-creating and testing these in SQL.

Unfortunately I know very little about pass thru queries and wouldn't know where to start on that.  This is the code I'm using to convert this data set currently.  The input recordset is being done thru a SPROC but all other processing is local.  Could you please show how this would look if done with a passthru query?

It's very simple code but runs a very long time due to the record volume being processed.
Public Sub reformatNA_SPROCIn(passedRsOut As Recordset, passedMuniCode As Long, passedClearWorkTable As Boolean, passedShowMsg As Boolean)
'
DBEngine.SetOption dbMaxLocksPerFile, 1000000
''
Dim wkID  As Long
Dim wkMuniCode  As Long
Dim wkControlNumber  As Long
Dim wkSequenceNumber  As Long
Dim wkAddrLine1  As String
Dim wkAddrLine2  As String
Dim wkAddrLine3  As String
Dim wkAddrLine4  As String
Dim wkCity As String
Dim wkState As String
Dim wkZip As String
'
Dim wkUnMailableYN As Boolean
Dim wkBankruptcyYN As Boolean
Dim wkAddrCheckNeededYN As Boolean
Dim wkSelectForAct20YN As Boolean

Dim wkParseAddr As Variant

'
wkStartTime = Now
'
'///////////////////////////// Stored Procedure ///////////////////////////////////////
Dim rsIn2 As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd
    .CommandText = "aConvertspMuni_NA"
    .CommandType = adCmdStoredProc
    setSQLConnection
    .ActiveConnection = gConnection    '
    .CommandTimeout = cSQLSP_5MinTimeout  ' set timeout to 5 minutes (300 second)

     
    Set rsIn2 = New ADODB.Recordset
        With rsIn2
            .CursorLocation = adUseServer
            .CursorType = adOpenKeyset
            .LockType = adLockOptimistic
            .Open cmd
        End With
        '
'///////////////////////////// Stored Procedure ///////////////////////////////////////

        '
        While Not rsIn2.EOF
            '
            passedRsOut.AddNew
            '
            passedRsOut![ID] = Nz(rsIn2![ID], 0)
            passedRsOut![MuniCode] = passedMuniCode
            passedRsOut![ControlNumber] = Nz(rsIn2![ControlNumber], 0)
            passedRsOut![SequenceNumber] = Nz(rsIn2![SequenceNumber], 0)
            passedRsOut![AddrLine1] = cleanupAddrLines(Nz(rsIn2![AddrLine1], ""))
            passedRsOut![AddrLine2] = cleanupAddrLines(Nz(rsIn2![AddrLine2], ""))
            passedRsOut![AddrLine3] = cleanupAddrLines(Nz(rsIn2![AddrLine3], ""))
            passedRsOut![AddrLine4] = cleanupAddrLines(Nz(rsIn2![AddrLine4], ""))
                '
            wkParseAddr = Nz(rsIn2![AddrLine4], "")
            
            parseCityStateZip wkParseAddr, _
                              wkCity, _
                              wkState, _
                              wkZip
            
            passedRsOut![City] = wkCity
            passedRsOut![State] = wkState
            passedRsOut![Zip] = wkZip
            '
            wkUnMailableYN = False
            wkBankruptcyYN = False
            wkAddrCheckNeededYN = False
            wkSelectForAct20YN = False
            '
            checkUnMailBnkrptcyAddrCheckNeeded Nz(rsIn2![AddrLine1], ""), wkUnMailableYN, wkBankruptcyYN, wkAddrCheckNeededYN, wkSelectForAct20YN
            checkUnMailBnkrptcyAddrCheckNeeded Nz(rsIn2![AddrLine2], ""), wkUnMailableYN, wkBankruptcyYN, wkAddrCheckNeededYN, wkSelectForAct20YN
            checkUnMailBnkrptcyAddrCheckNeeded Nz(rsIn2![AddrLine3], ""), wkUnMailableYN, wkBankruptcyYN, wkAddrCheckNeededYN, wkSelectForAct20YN
            checkUnMailBnkrptcyAddrCheckNeeded Nz(rsIn2![AddrLine4], ""), wkUnMailableYN, wkBankruptcyYN, wkAddrCheckNeededYN, wkSelectForAct20YN
            '
            passedRsOut![UnMailable] = wkUnMailableYN
            passedRsOut![Bankruptcy] = wkBankruptcyYN
            passedRsOut![AddressCheckNeeded] = wkAddrCheckNeededYN
            passedRsOut![SelectForAct20] = wkSelectForAct20YN
            '
            passedRsOut.Update
            '
            rsIn2.MoveNext
        Wend
            
    Set .ActiveConnection = Nothing
            
End With
'
rsIn2.Close
Set rsIn2 = Nothing
If passedShowMsg Then
    MsgBox "Complete"
End If
'
wkCurrTime = Now
logExecutionInfo "Reformat Name And Address Records ADO", wkStartTime, wkCurrTime

End Sub

Open in new window

Mark WillsTopic AdvisorCommented:
You will probably find it remarkably similar - just a matter of fitting in with your style / comfort level etc...

I have created a fairly quick and dirty function for you called funky characters with a few different styles inside - notice how we can use either ' ' or space(1). syntax for creating a function is similar to stored procedure, but there is a lot you cannot do inside a function (like dynamic sql)
create function udf_funky_characters (@funkystring varchar(max))
returns varchar(max) as
BEGIN
  declare @cleanstring varchar(max)
  set @cleanstring = replace(@funkystring,char(10),' ')
  set @cleanstring = replace(replace(replace(replace(@cleanstring,char(13),' '),char(39),' '),char(34),' '),char(35),'')
  set @cleanstring = replace(@cleanstring,'@',space(1))
  return @cleanstring
END
GO

-- and then to test

select *,dbo.udf_funky_characters(DataColumn) as newdatacolumn
from YourTableName

Open in new window


code waiting to be converted and wrapped in a function by you :)
declare @wkaddr varchar(100) = '12345678901234567890123456789*0123456'
If len(@wkAddr) > 29 
begin
    If substring(@wkAddr, 30, 1) in ('*','b')  
	   set @wkaddr = stuff(@wkAddr,30,1,space(0))
end
select @wkaddr

Open in new window

And will look at the last one a bit later (need sleep)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Mark WillsTopic AdvisorCommented:
Would help if you could post some samples (including some tricky ones) for the address/city/zip split....

And how did you go with the above - does it make sense ?
Mark WillsTopic AdvisorCommented:
And, that unwanted character in the 30th position could be updated directly, no real need for a function.

update yourtable set yourcolumn = stuff(yourcolumn,30,1,space(0))
where len(yourcolumn) > 29 and substring(yourcolumn, 30, 1) in ('*','b')

Open in new window


Any progress on samples ?
Any feedback so far ?
mlcktmguyAuthor Commented:
Thanks for all of  the suggestions.  I have not had time to try any of them yet.  I will keep you posted.
mlcktmguyAuthor Commented:
Finally getting to try these but I got stumped right away.  I right clicked on 'functions' , then 'new'.  It now wants me to select from 'Inline Table Function', 'Multi Statement Table valued function' or 'Scalar valued function'.  Not sure which to choose.

My thought was to create a function that called in multiple SP's.  IF I can get this to work I will use the 'Remove Funky Characters' function when processing several different tables.
mlcktmguyAuthor Commented:
Thanks Mark, finally got time ot ry this and it is very similar to the Access functions.

Would you happen to know the CHR(xx) representations of vbCRLF or vbCr?
Mark WillsTopic AdvisorCommented:
Linefeed = CHAR(10)
carriage return = CHAR(13)


Keep one of these handy :)  http://www.asciitable.com/
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.