SQL Azure – Creating a numbers table using a Cartesian Product and then expanding dates between two field values for individual records

The following code uses a Common Table Expression to create a numbers table ,which is then used to expand all dates between two date values of an individual record. This structure could be used in a variety of circumstances where you are needing to charge costs to a product over a period of time or if you are needing to record consecutive dated values between two dates.

I am using Whisky Casks as an example here. Imagine a cask came in on a day and needs to be matured for a period and there will be a charge for every day that the cask is being stored to an account. Clearly going forward you might want to set up an overnight process to create the daily charges. But what happens where you wish to retrospectively create charges. Charge records will build up quickly and it will be difficult to manually keep on top of the required record correction.

Firstly I create a table that will hold a single record for each date a cask is in bond between the two tables. There are additional columns here as I need them for my use case (casks in bond).

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[t066tempexpand](
	[pkid] [int] IDENTITY(1,1) NOT NULL,
	[chargedate] [datetime] NULL,
	[accountcode] [nvarchar](16) NULL,
	[pkidt002] [int] NULL,
	[pkidt053dailystoragecharge] [int] NULL,
	[locationwarehouse] [nvarchar](50) NULL,
 CONSTRAINT [PK_0000temp] PRIMARY KEY CLUSTERED 
(
	[pkid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Next we create a CTE numbers table and use this to explode a single record for each dated day between the two dates in this case bondindate and a disgorge date. I have an extra filter here that filters what casks are being put into the table (here pkid>2000). Other filters could be where disgorgedate is not null. The following line will be used to identify the charge date.
dateadd(day,v.number,d.bondindate)
and
join Numbers v on v.number between 0 and datediff(day, bondindate, disgorgedate)
The first line shows the date that will be charged. The join is particularly elegant as it filters or restricts the records within the numbers table stored as a common table expression to multiple records of an individual cask based on all dates between two field values. The starting date identifies where to explode from and the second identifies the ending date (in this case the disgorge date) identifies where it must end. Another example if you were wanting to bring it up to todays date or the day before today , if you have an overnight process running at midnight getdate()-1.

The cross join in the common table expression definition is used to create a cartesian join on itself, from my investigations there is no particular significance to the sys.columns table used other than this is a system table that holds all columns in all tables in the database and therefore is likely to be somewhat large. It so happens in the database that I am using there are a lot of tables and a lot of columns so a single self cross join creates more than enough numbers than I will ever need. If you wish to create further records you can further cross join the sys.columns table on itself by repeating cross join line with subsequent aliases eg (cross join sys.columns c). After experimentation I discovered for instance if I left out the cross join there were only some 4,000 records in my numbers table. This is suspect reflects the fact that I have 4,000 columns in the tables of my database. In my case 4,000 X 4,000 or the cartesian product of the sys.columns table on itself results in 16 million records more than enough numbers for me. It should be noted that I am restricting the CTE numbers table to the top 20,000 anyway which in our case relates to over 54 years which is more back dating than I think I will ever need. It should be noted the original code I discovered had three cross joins presumably because the number of tables and so columns in the database was much smaller.

;WITH Numbers(number) AS (
  select top(20000) row_number() over (order by (select 0))-1
  from sys.columns a
  cross join sys.columns b
)

INSERT into t066tempexpand
(chargedate,
accountcode,
pkidt002,
pkidt053dailystoragecharge,
locationwarehouse)
select 
dateadd(day,v.number,d.bondindate) 
chargedate,
accountcode, 
pkid,
pkidt053dailystoragecharge,
locationwarehouse
  from t002caskfill d
  join Numbers v on v.number between 0 and datediff(day, bondindate, disgorgedate)
  where pkid > 2000

Here I am joining with an extra table to find out the daily storage charge and this is just a test.

select
a.chargedate,
a.accountcode,
a.pkidt002,
b.dailycharge,
a.pkidt053dailystoragecharge,
a.locationwarehouse,
b.chargecode
from dbo.t0000temp a
join dbo.v128dailycaskstoragelk023 b on a.pkidt053dailystoragecharge=b.pkid 
order by pkidt002,chargedate;

And lastly I insert all this information into a Charge history table.

INSERT INTO dbo.t0032chargehistory
(chargedate,
accountcode,
pkidt002cask,
chargedamount,
pkidt053chargecode,
locationwarehouse,
chargecode)
select
a.chargedate,
a.accountcode,
a.pkidt002,
b.dailycharge,
a.pkidt053dailystoragecharge,
a.locationwarehouse,
b.chargecode
from dbo.t066tempexpand a
join dbo.v128dailycaskstoragelk023 b on a.pkidt053dailystoragecharge=b.pkid 
order by pkidt002,chargedate;

SQL Azure – Create a Function that uses multiple If Else statements

Example create function code / SQL Azure / that uses the IF ELSE statement to calculate dates between three parameters(referenced to fields) to determine an integer difference between dates – in this case whisky in a bond we are calculating number of days and the parameters are – date cask arrived in bond, date cask out of the bond and date cask is disgorged.
In English I am calculating number of days in a bond from the bond in date to todays date if there is no bond out date or disgorge date. If either of those dates exist the number of days in bond is the bond in date to the earlier of either of those dates.

I add in an extra day so that if a cask comes in on Tuesday and goes out the following days the number of days is counted as 2.

Clearly this is a nice structure that can be used in multiple contexts with different factors. I use the most common option in the first switch in the thought that this will speed things up on the server.

CREATE FUNCTION [dbo].[calculatedaysinbondcorrect]
(
@datebondin Date,
@datebondout Date,
@datedisgorge Date
)
RETURNS integer
as
BEGIN
DECLARE @date1 DATE, @date2 DATE, @date3 DATE;
DECLARE @daysinbond INT;

SET @date1 = DATEADD(DAY,-1,@datebondin)
SET @date2 = @datedisgorge
SET @date3 = @datebondout

IF ((@date2 is null) and (@date3 is null))
	BEGIN 
		SET @date2 = getdate()
	END
ELSE If ((@date3 is null) and (@date2 is not null))
	Begin
		Set @date2 = @datedisgorge
	END
ELSE IF  ((@date3 is not null) and (@date2 is null))
    BEGIN
		SET @date2 = @datebondout
	END	
ELSE IF (@date2 > @date3)
	BEGIN 
		SET @date2 = @datebondout
	END
ELSE IF (@date2 < @date3)
	BEGIN 
		SET @date2 = @datedisgorge
	END
ELSE IF (@date2 = @date3)
	BEGIN 
		SET @date2 = @datedisgorge
	END

SELECT @daysinbond = DATEDIFF(dd, @date1, @date2)

RETURN @daysinbond;
END

SQL Azure – Azure Automation Hybrid Worker Setup and RunAs Account

The highs and lows.

You’re flying high – you’ve created a stored procedure that you’ve managed to reference in your automation account within a powershell runbook and you’ve got it nicely churning over on an extremely regular basis. You want to run it daily or weekly but as a test you run it hourly just to ensure that it works before releasing it to production. A day passes ok and you think wow this is really great. Then you forget about it and come back to it a week later. You find that somehow it stopped working at 1 am on Tuesday.

What happened????

Sorry you got hit by a block resulting from dynamic IP change on the runbook in the Automation Account – DAMN.
(there is probably a good reason for having a dynamic IP on automation accounts but damn it was really going too well)

So after quite a lot of investigation I found those in the know seemed to be saying the solution is.
Azure Automation linked to a Hybrid Worker

I kept investigating this and I kept getting directed to Microsoft documentation which although really extensive was just not helping me really get my head around things. It wasn’t until I found Travis Roberts that things started to click… I link to three of his videos below.

So the downsides of Hybrid Worker setup..

  • Despite being the recommended solution configuration is complicated.
  • You need to create a VM with a static IP just to run the hybrid worker (and then grant firewall access to the databaseserver for that VM)
  • That VM needs to have 2 cores and 4Gb of Ram (yes unfortunately not the smallest VM so you could be looking at $25 a month charge)
  • You need to set up log analytics
  • It is recommended that log analytics is in the SAME Azure location as the sample database BUT not all Azure locations have log analytics set up… Use UK South! Hands up who has already set up their server and just realised it is in a location that doesn’t allow log analytics.
  • So I was really struggling with this after my runbook unexpectedly started failing. After several days of reading and searching I found Travis Roberts. Hallelujah for MVPs.

    So I haven’t done it yet but I think the procedure can all be defined in here.

    1. Setting up an Azure VM with a fixed IP (It can be any VM but Azure is good for me)
    Setup VM with static IP
    The server you setup will need to have internet connectivity with SSL outbound on port 443

    2. Setting up Azure Log Analytics a step by step guide
    Setting up Azure Log Analytics a step by step guide

    3. Azure Automation Setup and Linking Log Analytics to your Automation account
    Set up Azure Automation and link it to Log Analytics

    4. Configuring an Azure Automation Hybrid Runbook Worker to run your runbook.
    Installation of Hybrid Runbook Worker
    Includes information about Runbook Worker Group and
    RunAsAccount
    You add credentials to the Runbook Worker Group

    Firstly a big thank you to Travis Roberts for putting out those videos he has loads of Azure Centric content on his youtube channel which you can find here

    CAVEAT I haven’t implemented this myself properly and at the time of writing(December 2021) those videos are a couple of years old now. At the start of one of the Videos Travis indicates that things are changing quickly in the space and expect that there has been changes since these videos. Lets face it this should really be easier to do Azure is just frustrating their users with this complicated configuration.

    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;

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

    I think this one is pretty dirty but here goes.

    Again it was adapted from StackOverflow thread
    How to get part of string that matches with regular expression in SQL Server

    Continuing the theme of identifying substrings in NVARCHAR(MAX) field here is a set of Functions that can be used identify and separate out money in an email body which has been uploaded into a NVARCHAR(MAX) field. looks for the pattern of 2 numbers after a dot and certain numerals before a dot and steps down from hundreds of thousands of pounds to tens of pounds. Note the order is important of the final query as each shorter pattern is a subset the next longer pattern.

    First important to strip out the commas in the body as this will skip any currencies which have been formatted with commas.

    UPDATE T032email
    SET body = REPLACE(body,',','');

    WARNING It should be noted that this is very unoptimised code and even on small sets combining the functions in a nested ISNULL SQL really takes time to run.

    Firstly a function that looks for the pattern 100000.00 ie 999k to 100k.

    CREATE FUNCTION [dbo].[ReturnMoneyDecimal6](@fieldtosearch as nvarchar(max))
     Returns varchar(9)
     as
     begin
     declare @length as int
     declare @loop as int
     declare @retemoney6 as decimal(9,2)
     set @loop =1
     set @length = len(@fieldtosearch)
     
     while @loop<=@length-8
        begin
        if exists(select 1 where substring(@fieldtosearch,@loop,9) like '[0-9][0-9][0-9][0-9][0-9][0-9][.][0-9][0-9]')
            set @retemoney6 = substring(@fieldtosearch,@loop,9)
        set @loop=@loop+1
        end
    Return @retemoney6
    END
    

    Next a function that looks for the pattern 10000.00 ie 99k to 10k.

    CREATE function [dbo].[ReturnMoneyDecimal5](@fieldtosearch as nvarchar(max))
     Returns varchar(8)
     as
     begin
     declare @length as int
     declare @loop as int
     declare @retemoney5 as decimal(8,2)
     set @loop =1
     set @length = len(@fieldtosearch)
     
     while @loop<=@length-7
        begin
        if exists(select 1 where substring(@fieldtosearch,@loop,8) like '[0-9][0-9][0-9][0-9][0-9][.][0-9][0-9]')
            set @retemoney5 = substring(@fieldtosearch,@loop,8)
        set @loop=@loop+1
        end
    Return @retemoney5
    END
    

    Next a function that looks for the pattern 1000.00 ie 9k to 1k.

    CREATE function [dbo].[ReturnMoneyDecimal4](@fieldtosearch as nvarchar(max))
     Returns varchar(7)
     as
     begin
     declare @length as int
     declare @loop as int
     declare @retemoney4 as decimal(7,2)
     set @loop =1
     set @length = len(@fieldtosearch)
     
     while @loop<=@length-6
        begin
        if exists(select 1 where substring(@fieldtosearch,@loop,7) like '[0-9][0-9][0-9][0-9][.][0-9][0-9]')
            set @retemoney4 = substring(@fieldtosearch,@loop,7)
        set @loop=@loop+1
        end
    Return @retemoney4
    END
    

    Next a function that looks for the pattern 100.00 ie 999 to 100.

    CREATE function [dbo].[ReturnMoneyDecimal3](@fieldtosearch as nvarchar(max))
     Returns varchar(6)
     as
     begin
     declare @length as int
     declare @loop as int
     declare @retemoney3 as decimal(6,2)
     set @loop =1
     set @length = len(@fieldtosearch)
     
     while @loop<=@length-5
        begin
        if exists(select 1 where substring(@fieldtosearch,@loop,6) like '[0-9][0-9][0-9][.][0-9][0-9]')
            set @retemoney3 = substring(@fieldtosearch,@loop,6)
        set @loop=@loop+1
        end
    Return @retemoney3
    END
    

    Lastly a function that looks for the pattern 10.00 ie 99 to 10.

    CREATE function [dbo].[ReturnMoneyDecimal2](@fieldtosearch as nvarchar(max))
     Returns varchar(5)
     as
     begin
     declare @length as int
     declare @loop as int
     declare @retemoney2 as decimal(5,2)
     set @loop =1
     set @length = len(@fieldtosearch)
     
     while @loop<=@length-4
        begin
        if exists(select 1 where substring(@fieldtosearch,@loop,5) like '[0-9][0-9][.][0-9][0-9]')
            set @retemoney2 = substring(@fieldtosearch,@loop,5)
        set @loop=@loop+1
        end
    Return @retemoney2
    END
    

    And then here is the quite horribly inefficient query that can be used to run all functions and seems to do the job.

    You know your searchstrings better than me but any text with multiple decimal substrings will be an issue!!!

    Which is still a few million times quicker than any person!

    SELECT dbo.T032email.pkid,
    ISNULL(dbo.ReturnMoneyDecimal6(dbo.T032email.body),
    	ISNULL(dbo.ReturnMoneyDecimal5(dbo.T032email.body),
    		ISNULL(dbo.ReturnMoneyDecimal4(dbo.T032email.body),
    			ISNull(dbo.ReturnMoneyDecimal3(dbo.T032email.body), dbo.ReturnMoneyDecimal2(dbo.T032email.body)
    				)))) as money4 from T032email;

    SQL Azure – TSQL User Defined Function – Cleaning a Field of Specified Characters

    Stack overflow sourced, adapted and personally tested code
    How to strip all non-alphabetic characters from string in SQL Server

    In an earlier post I was having issues as some email addresses I was pulling out from a field were captioned in <> using the following will remove those characters prior to identifying string patterns..

    Remember this pulls them out and then compacts the resulting string this may or many not be what you are looking for. I have adapted from the Stack Overflow discussion to include characters I don’t want rid of.

    CREATE Function [dbo].[RemoveNonAlphaCharacters](@Temp nvarchar(max))
    Returns nvarchar(max)
    AS
    Begin
    
        Declare @KeepValues as nvarchar(50)
        Set @KeepValues = '%[^a-z0-9/@ £$+=?.\!]%'
        While PatIndex(@KeepValues, @Temp) > 0
            Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')
    
        Return @Temp
    End

    And running the example code we get..

    SELECT dbo.RemoveNonAlphaCharacters('abcdefghijklmnopqrstuvwxyz1234567890 !"£$()_+=-{}[]:@~;\|<>?./') as txtCorrected

    We get

    WARNING Please note ^ % & and * ‘ ” – are reserved TSQL characters and including them in the function appears to break the logic that I wish to see in the pattern replacement.

    SQL Azure – TSQL User Defined Function – Separate multiple emails from NVARCHAR(MAX) field

    Stack overflow sourced, adapted and personally tested code
    Extract email address from string using tsql

    A continuation of working with strings in TSQL specifically linked to emails.

    Firstly create the following Function

    CREATE FUNCTION [dbo].[fnFindPatternLocation]
    (
        @string NVARCHAR(MAX),
        @term   NVARCHAR(MAX)
    )
    RETURNS TABLE
    AS
        RETURN 
        (
            SELECT pos = Number - LEN(@term) 
            FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@string, Number, 
            CHARINDEX(@term, @string + @term, Number) - Number)))
            FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
            FROM sys.all_objects) AS n(Number)
            WHERE Number > 1 AND Number <= CONVERT(INT, LEN(@string))
            AND SUBSTRING(@term + @string, Number, LEN(@term)) = @term
        ) AS y);

    Then create a View of what you are interested in as follows.. Note here I am taking out the carriage return as my subsequent query doesn’t like them and in emails they frequently exist.

    CREATE VIEW [dbo].[v001] as SELECT pkid, REPLACE(body, CHAR(13) + CHAR(10),' ') as body1 from t001email
    

    Then run the newly created View through a query.

    SELECT pkid, body1, pos, SUBSTRING(body,beginningOfEmail,endOfEmail-beginningOfEmail) AS email
    FROM v001
    CROSS APPLY (SELECT pos FROM dbo.fnFindPatternLocation(body1, '@')) AS A(pos)
    CROSS APPLY (SELECT CHARINDEX(' ',body1 + ' ', pos)) AS B(endOfEmail)
    CROSS APPLY (SELECT pos - CHARINDEX(' ', REVERSE(SUBSTRING(body, 1, pos))) + 2) AS C(beginningOfEmail)
    

    Couple of things here
    Multiple emails will be picked out and placed as separate records so if there a string that reads

    This is a sentence with two emials first@gmail.com and a second second@gmail.com

    it will return
    first@gmail.com
    second@gmail.com

    If an email starts the field then this will NOT work after finding the @ symbol it will count forward and fail to find a space and so set space before to Null it will then return just the domain of the email. I will be looking to fix this at some point.

    Secondly if the emails within the field contain contiguous special html characters such as < or > these will be picked up and inculded as if they are part of the email addresses.

    We can fix this by scanning through the varchar(max) field and stripping out special characters.

    NOTE : If you are working with email bodies carriage returns will also screw up the above query in which case consider running the field through some kind of replace view with similar syntax as

    CREATE VIEW v002 as SELECT pkid, REPLACE(body, CHAR(13) + CHAR(10),' ') as txtBodyWithoutReturns from t001email

    SQL Azure – TSQL User Defined Function – Separate Defined Length String from NVARCHAR(MAX) Field

    Stack overflow sourced, adapted and personally tested code
    How to get part of a string that matches with a regular expression

    The following function arose out of a desire to find Eplanning Scotland planning references numbers which follow the format of 9 numerals a dash and then 3 numerals within a NVARCHAR(MAX) field type. The characteristics of the string was that it is always the same length.

    In SSMS select the New Query button

    Create function [dbo].[ReturnEplanningNumberFull](@fieldtosearch as nvarchar(max))
     Returns nvarchar(13)
     as
     begin
     declare @length as int 
     declare @loop as int
     declare @reteplan as varchar(13)
     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][0-9][0-9][0-9][0-9][0-9][0-9][0-9][-][0-9][0-9][0-9]')
            set @reteplan = substring(@fieldtosearch,@loop,13)
        set @loop=@loop+1
        end
    Return @reteplan
    end

    And then to create a View using this you can write something like ;

    CREATE VIEW v026eplanrefs AS 
    SELECT dbo.THETABLE.pkid, 
    dbo.ReturnEplanningNumberFull(dbo.THETABLE.FIELDCONTAINSREFERENCE) as eplanno 
    FROM dbo.THETABLE;
    

    I subsequently altered this to identify the first 9 digits as this is sufficent to uniquely identify eplanning records.

    CREATE function [dbo].[ReturnEplanningNumberShort](@fieldtosearch as nvarchar(max))
     Returns nvarchar(9)
     as
     begin
     declare @length as int 
     declare @loop as int
     declare @reteplanshort as nvarchar(9)
     set @loop =1
     set @length = len(@fieldtosearch)
    
     while @loop<=@length-8
        begin
        if exists(select 1 where substring(@fieldtosearch,@loop,9) like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
            set @reteplanshort = substring(@fieldtosearch,@loop,9)
        set @loop=@loop+1
        end
    Return @reteplanshort
    end