Category: Data Cleansing

SQL Azure – TSQL User Defined Function – Pull out Long Dates from NVARCHAR(MAX)

Dates that match the pattern 2 numbers a single space the month a single space then a 4 digit year this will be good for any dates following this format from 1000 AD to 9999AD with a NVARCHAR(MAX) field note it will only pick up the last date in a text field.

Sourced adapted and tested from Stack Overflow

*Please note the square brackets c square brackets string is being omitted for months containing the letter c (I believe it relates to html and the copyright symbol I can’t place it anywhere in my post). You will need to add it back in to those months that contain c. I have therefore replaced square brackets c square brackets with [k]

January

Create function [dbo].[m01returnjandates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(15)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retjandate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-14
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,15) like '[0-9][0-9][ ][J][a][n][u][a][r][y][ ][0-9][0-9][0-9][0-9]')
        set @retjandate= substring(@fieldtosearch,@loop,15)
    set @loop=@loop+1
    end
Return @retjandate
End

===
February

Create function [dbo].[m02returnfebdates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(16)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retfebdate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-15
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,16) like '[0-9][0-9][ ][F][e][b][r][u][a][r][y][ ][0-9][0-9][0-9][0-9]')
        set @retfebdate= substring(@fieldtosearch,@loop,16)
    set @loop=@loop+1
    end
Return @retfebdate
End

====
March

Create function [dbo].[m03returnmardates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(13)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retmardate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-12
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,13) like '[0-9][0-9][ ][M][a][r][k][h][ ][0-9][0-9][0-9][0-9]')
        set @retmardate= substring(@fieldtosearch,@loop,13)
    set @loop=@loop+1
    end
Return @retmardate
End

===
April

Create function [dbo].[m04returnaprdates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(13)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retaprdate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-12
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,13) like '[0-9][0-9][ ][A][p][r][i][l][ ][0-9][0-9][0-9][0-9]')
        set @retaprdate= substring(@fieldtosearch,@loop,13)
    set @loop=@loop+1
    end
Return @retaprdate
End

===
May

Create function [dbo].[m05returnmaydates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(11)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retmaydate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-10
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,11) like '[0-9][0-9][ ][M][a][y][ ][0-9][0-9][0-9][0-9]')
        set @retmaydate= substring(@fieldtosearch,@loop,11)
    set @loop=@loop+1
    end
Return @retmaydate
End

===
June

Create function [dbo].[m06returnjundates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(12)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retjundate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-11
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,12) like '[0-9][0-9][ ][J][u][n][e][ ][0-9][0-9][0-9][0-9]')
        set @retjundate= substring(@fieldtosearch,@loop,12)
    set @loop=@loop+1
    end
Return @retjundate
End

===
July

Create function [dbo].[m07returnjuldates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(12)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retjuldate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-11
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,12) like '[0-9][0-9][ ][J][u][l][y][ ][0-9][0-9][0-9][0-9]')
        set @retjuldate= substring(@fieldtosearch,@loop,12)
    set @loop=@loop+1
    end
Return @retjuldate
End

===
August

Create function [dbo].[m08returnaugdates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(14)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retaugdate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-13
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,14) like '[0-9][0-9][ ][A][u][g][u][s][t][ ][0-9][0-9][0-9][0-9]')
        set @retaugdate= substring(@fieldtosearch,@loop,14)
    set @loop=@loop+1
    end
Return @retaugdate
End

===
September

Create function [dbo].[m09returnsepdates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(17)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retsepdate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-16
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,17) like '[0-9][0-9][ ][S][e][p][t][e][m][b][e][r][ ][0-9][0-9][0-9][0-9]')
        set @retsepdate= substring(@fieldtosearch,@loop,17)
    set @loop=@loop+1
    end
Return @retsepdate
End

===
October

Create function [dbo].[m10returnoctdates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(15)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retjoctdate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-14
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,15) like '[0-9][0-9][ ][O][k][t][o][b][e][r][ ][0-9][0-9][0-9][0-9]')
        set @retoctdate= substring(@fieldtosearch,@loop,15)
    set @loop=@loop+1
    end
Return @retoctdate
End

===
November

Create function [dbo].[m11returnnovdates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(16)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retnovdate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-15
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,16) like '[0-9][0-9][ ][N][o][v][e][m][b][e][r][ ][0-9][0-9][0-9][0-9]')
        set @retnovdate= substring(@fieldtosearch,@loop,16)
    set @loop=@loop+1
    end
Return @retnovdate
End

===
December

Create function [dbo].[m12returndecdates](@fieldtosearch as nvarchar(max))
 Returns nvarchar(16)
 as
 begin
 declare @length as int
 declare @loop as int
 declare @retdecdate as date
 set @loop =1
 set @length = len(@fieldtosearch)
 
 while @loop<=@length-15
    begin
    if exists(select 1 where substring(@fieldtosearch,@loop,16) like '[0-9][0-9][ ][D][e][k][e][m][b][e][r][ ][0-9][0-9][0-9][0-9]')
        set @retdecdate= substring(@fieldtosearch,@loop,16)
    set @loop=@loop+1
    end
Return @retdecdate
End

===
And the inefficent example TSQL

SELECT dbo.T032email.pkid,
ISNULL(dbo.m01returnjandates(dbo.T032email.body),
    ISNULL(dbo.m02returnfebdates(dbo.T032email.body),
        ISNULL(dbo.m03returnmardates(dbo.T032email.body),
            ISNull(dbo.m04returnaprdates(dbo.T032email.body), 
				ISNULL(dbo.m05returnmaydates(dbo.T032email.body),
					ISNULL(dbo.m06returnjundates(dbo.T032email.body),
					ISNULL(dbo.m07returnjuldates(dbo.T032email.body),
					ISNull(dbo.m08returnaugdates(dbo.T032email.body,					 
                                        ISNULL(dbo.m09returnsepdates(dbo.T032email.body),
					ISNULL(dbo.m10returnoctdates(dbo.T032email.body),
	ISNULL(dbo.m11returnnovdates(dbo.T032email.body), dbo.m12returndecdates(dbo.T032email.body)
				))))))))))) as trandate FROM T032email;

MS Access Function : Automate Normalisation or De-concatenate a field (MS Outlook export example)

I was contemplating a better way of storing our old emails and unhappy with some of the systems in place I started considering whether I could dump them into a database.

Thing is when you export from Outlook some of the standard fields in particular To and From are concatenated. Wouldn’t it be nice to separate those fields into their own table of names and addresses and reference them back to a main table of messages.

This is what I came up with.

For demonstrations purposes I will use two tables

t001parent
pkid - autonumber primary key
ccaddresses - memo or long text

and the child table

t002newchildren
pkid - autonumber primary key
ccaddress - string(150 should do it)
pkidt001 - number

and here is the blank second table

Next we create a user defined function

Public Function CreateChildTable()

    Dim db          As DAO.Database
    Dim rs          As DAO.Recordset
    Dim rsTarget    As DAO.Recordset

    Dim strSQL      As String
    Dim strField1   As String
    Dim strField2   As String
    Dim varData     As Variant
    Dim i           As Integer
    Dim intPKID     As Integer

    Set db = CurrentDb

    'Select all the ccaddresses from the parent table
    strSQL = "SELECT pkid,ccaddresses FROM t001parent"

    Set rsTarget = db.OpenRecordset("t002newchildren", dbOpenDynaset, dbAppendOnly)

    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    With rs
        While Not .EOF
            intPKID = !pkid
            varData = Split(!ccaddresses, ";") ' Get all semi colon delimited fields
               
            'add email addresses if there is only one email address there
            With rsTarget
            .AddNew
            !ccaddresss = Trim(varData(0)) ' remove spaces before writing new fields
            !pkidt001 = intPKID
            .Update
            End With
            

            'loop through addtional email addresses and add them as children to table
            For i = 1 To UBound(varData)
                With rsTarget
                    .AddNew
                    !ccaddresss = Trim(varData(i)) ' remove spaces before writing new fields
                    !pkidt001 = intPKID
                    .Update
                End With
            Next
            .MoveNext
        Wend

        .Close
        rsTarget.Close

    End With

    Set rsTarget = Nothing
    Set rs = Nothing
    db.Close
    Set db = Nothing

End Function

After running this we should

We’ve just nicely split the parent table ccaddesses field into constituent emails and normalised it as a child of the parent table.

MS Access Function : Print to excel spreadsheet field definitions of all tables in a database

This places all tables and fields into an excel file on a single worksheet as a single table.

Public Function TableDef()
Dim def As TableDef
Dim wb As Object
Dim xL As Object
Dim lngRow As Long
Dim f As Field
Set xL = CreateObject("Excel.Application")
xL.Visible = True
Set wb = xL.workbooks.Add
lngRow = 2
For Each def In CurrentDb.TableDefs
For Each f In def.Fields
With wb.sheets("Sheet1")
.Range("A" & lngRow).Value = def.Name
.Range("B" & lngRow).Value = f.Name
.Range("C" & lngRow).Value = f.Type
.Range("D" & lngRow).Value = f.Size
.Range("E" & lngRow).Value = f.Required
lngRow = lngRow + 1
End With
Next
Next
End Function

MS Access Function : Loop through tables and export to csv

A function that will loop through an access database and export all tables to csv and xls.

Useful for subsequent import through QGIS into Postgres.

Public Function ExportAll()
Dim obj As AccessObject, dbs As Object
Dim strFolder As String
strFolder = "c:\"
Set dbs = Application.CurrentData
For Each obj In dbs.AllTables
If Left(obj.Name, 4) <> "MSys" Then
DoCmd.TransferText acExportDelim, , obj.Name, strFolder & obj.Name & ".csv", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, obj.Name, strFolder & obj.Name & ".xls", True
End If
Next obj
End Function

Using SQL to parse, clean and format strings

Many datasets can be somewhet confused by the time you get them. Maybe you had no control of the export from the database or maybe you asked for the right information and it came back somewhat warped.

SQL has powerful fuctions that can pretty much clean things up however you would like.

We can use multiple SQL commands within an MS Access module to clean up a source by placing them consecutively within a module here is the structure of some of the queries that I use.

UPDATE SELECTED FIELDS BASED ON A MATCHED STRING IN ANOTHER FIELD
SQL that updates Town and PostalTown fields based on a string in an aggregated PostalAdd field.
Please note that _ sign denotes a movement to another line within the VB Module required to make the SQL String run correctly. This may require alteration if you are cutting and pasting from this page.

Dim SQL As String    
SQL = "UPDATE Table01 SET Table01.Town = 'Barassie', Table01.PostalTown = 'TROON' " & _
"WHERE (((Table01.PostalAdd) Like '*Barassie, TROON*'));"
DoCmd.RunSQL SQL

CONVERT A STRING FIELD TO ALL CAPITALS, ALL CAMEL CASE OR ALL LOWER CASE
The following SQL converts the street field of Table01 to all capitals. This could be run like the previous SQL from within an MS Access module

Dim SQL1 As String
SQL1 = "UPDATE Table01 SET Table01.Street = StrConv([Table01].[Street],1);"
DoCmd.RunSQL SQL1

In the above code change the trailing number parameter to select type of alteration
1 – ALL CAPIALS
2 – all lower case
3 – Camel Case

PARSE OUT LEFT PART OF STRING BY LOCATING UNIQUE CHARACTER OR STRING
This looks to the Yourfieldname field of TableRainbow and searches from the left for a comma and returns everything to the left into a field called LeftParse

Dim SQL2 As String
SQL2 = "SELECT Left$([Yourfieldname],InStr(1,[Yourfieldname],",")-1) AS LeftParse FROM TableRainbow;"
DoCmd.RunSQL SQL2

PARSE OUT RIGHT PART OF STRING BY LOCATING UNIQUE CHARACTER OR STRING
If you have a string with commas this string will count the length of the string then count the number of characters to your unique string – in this case a comma – and then return all characters from that string to the right of that comma.

The below code looks to the Yourfieldname of TableRainbow counts its length and then find the first comma from the right and returns the information as a select query result in a field named Right Parse. It should be noted that it searches through the target field searching from the left. IF there are multiple commas then it will stop counting when it hits the first comma. You can substitute the right part of the function with a number.

Dim SQL3 As String
SQL3 = "SELECT Right$([Yourfieldname],Len([Yourfieldname])-InStr(1,[Yourfieldname],",")-1) AS RightParse FROM TableRainbow;"
DoCmd.RunSQL SQL3

Towards the best table design for Addresses

Floor Reference or Flat No
Building Name or House Name
House/Building/Unit Number
Street Name
Through Street
Locality
Town or City
Country
Postcode

A designer knows he has achieved perfection not when there is nothing left to add, but when there is nothing left to take away.

Antoine de Saint-Exupery

OK so I didn’t know who he was either until I did a search on simplicity but I wanted to record my best field structure for the storage of addresses which although important is on a par with watching paint dry in terms of excitement. Despite this addresses are central to a heck of a lot of applications so it is important to get it right. This copes with pretty much most addresses. I mainly use locality in larger towns or cities to denote a sub area specifically for users as you can guarantee they will be unfamiliar with the majority of specific street names and particularly for users who are doing business in the same city they are located in it is very useful for them to have a quick understanding of where things are in relation to the assets of the operation involved. Such “context” can be added by using maps but will come for free with intelligent staff if you give them opportunity. I long ago dropped counties and districts but I could see if you were in a larger country such as Russia or America could be useful to note state / oblast etc.. Additionally in the UK town is really postal town – which is often not obvious at all – it can be overkill but you can organise addresses so that postal town is referenced from the town automatically. Probably overkill.

Information on BS7666
National Land and Property Gazeteer