Creating/Using Functions in SQL Server

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.

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
        wkStr = Mid(wkCityStZip, i, 1)
        wkIsANumber = isANumber(wkStr)
        If wkIsANumber Then
            returnZip = wkStr & returnZip
            wkZipBeginPos = i
        End If
    End If
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
        wkStr = Mid(wkCityStZip, i, 1)
        If wkStr = Space(1) Or wkStr = "," Then             'bypass spaces moving back
            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
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

Who is Participating?

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

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.

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
    .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![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, _
            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
    Set .ActiveConnection = Nothing
End With
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
  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

-- 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 
    If substring(@wkAddr, 30, 1) in ('*','b')  
	   set @wkaddr = stuff(@wkAddr,30,1,space(0))
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 :)
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

From novice to tech pro — start learning today.