MS Azure & Web Applications – TSQL Create a check constraint on nvarchar column specifying a minimum length in DB and through the front end ensure enforce OWASP security

Double security!
Enforce length in database and then through the front end by validating in this case password values (PHPRunner example).

First the backend
You have a nvarchar(128) column in MS Azure SQL Database (or any Microsoft SQL Server database) and you wish to add a check constraint that does not allow a value in the column to be less than lets say 15 characters long.

tablename = users
field = userpassword

ALTER TABLE dbo.users
ADD CONSTRAINT [MinLengthConstraint] 
CHECK (DATALENGTH([userpassword]))>14;

And then a good way to check the constraints on an individual table using TSQL through SSMS for MS AZURE.

SELECT TABLE_NAME, 
CONSTRAINT_TYPE, 
CONSTRAINT_NAME 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE TABLE_NAME ='users';

Front End
Now for double security in your web application use a regular expression to prevent users entering a weak password.

^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{15,128}$
^               # start of the string
(?=.*\d)        # a digit must occur at least once
(?=.*[a-z])     # a lower case letter must occur at least once
(?=.*[A-Z])     # an upper case letter must occur at least once
.{15,128}       # 15-128 character password must be a minimum of 15 characters and a maximum of 128
$               # end of the string

OWASP – Open Web Application Security Project

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 – Script to identify all Users in a SQL Azure DATABASE and how to remove Users

SELECT name AS username, 
     create_date, modify_date, 
     type_desc AS type, 
     authentication_type_DESC AS authentication_type 
FROM sys.database_principals 
WHERE type NOT IN ('A', 'G', 'R', 'X') 
     AND
          sid IS NOT null ORDER BY username;

And to remove a user one of the easiest ways both to see all the users and remove any of the individual users. After connecting to your Azure Database within the database in question identify the Security Branch and then the Users branch – right click on the user you wish to remove and select DELETE.

This follows on from the post whereby we were trying to creat a user see here
Creating Contained Users

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 – Using Azure Automation and Runbooks to Schedule an Azure SQL Stored Procedure

    Its vital to be able to run stored procedures on a schedule it can be extremely important for doing daily housekeeping.

    This site was where I learned how to do this.
    How to Schedule an Azure SQL Database’s Stored Procedure

    Runbooks sit in Azure Automation and a Runbook consists of a script that performs a task.

    Runbooks can be written in Python or Powershell. See the last part of this post for an important additional issue.

    "Data Source=01server.database.windows.net;Initial Catalog=database;Integrated Security=False;User ID=01userid;Password=JK12jl423;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"
    
    # Instantiate the connection to the SQL Database
    $sqlConnection = new-object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = "Data Source=servername.database.windows.net;Initial Catalog=database;Integrated Security=False;User ID=userid;Password=password;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False"
    
    $sqlConnection.Open()
    Write-Output "Azure SQL database connection opened"
    
    # Define the SQL command to run
    $sqlCommand = new-object System.Data.SqlClient.SqlCommand
    $sqlCommand.CommandTimeout = 120
    $sqlCommand.Connection = $sqlConnection
    
    Write-Output "Issuing command to run stored procedure"
    
    # Execute the SQL command
    $sqlCommand.CommandText= 'exec [dbo].[storedProcedure]'
    $result = $sqlCommand.ExecuteNonQuery()
    Write-Output "Stored procedure execution completed"
    
    # Close the SQL connection
    $sqlConnection.Close()
    
    Write-Output "Run completed"
    

    Honestly the hardest part is getting the code setting up the scheduling after that is relatively easy.

    WARNING : Runbooks have dynamic IPs so although you can set grant access to an IP your runbook will only run for a certain period before it stops working unless you either have a Hybrid Runbook Worker setup OR you allow Azure services and resources to access this server (which isn’t recommended) The next post I will do is my initial notes on how to configure a Hybrid Runbook Worker.

    SQL AZURE – Group records or widgets in Sets of a Specific Number

    Useful if you have factory products that are being packed into sets of a specfic number. In our case sixes.

    Swap out to a different amount if you need to. The last group in the record set will have a remainder level if the recordset is not perfectly divisible by the set integer.

    SELECT pkid,
    floor(((ROW_NUMBER() OVER(ORDER BY pkid))-1)/6)+1 as grpNum
    FROM t002caskfill
    

    Here is a screenshot of the above code being run on a table.

    SQL Azure – Convert UTC to your local time – and an interesting observation

    Not unsurprisingly SQL Azure GetDate() stores datetime as a UTC value.

    If your application is hosted in a specific country it is likely you will wish to return that value to the local user in the local time.
    Here I host in the UK and want to display in the UK so my choice is simple enough.

    The default CONVERT() statement in the SQL Azure library will do this for you.

    An example of the statement with parameters is here (slightly confusingly here I have the field name as a reserved word but just ensure that your field name is in the square brackets)

    CONVERT(datetime, 
    SWITCHOFFSET([datetime], 
    DATEPART(TZOFFSET, 
    [datetime] AT TIME ZONE 'GMT Standard Time'))) 
    as UKTime

    And here is that statement included in a full query.

    And here is an SQL statement to determine all time zones currently observing daylight saving on your server.
    This query can also be used to identify the pre-defined text string parameter you should have for your convert statement.

    SELECT
      name,
      current_utc_offset
    FROM sys.time_zone_info
    WHERE is_currently_dst = 1;
    

    Running this I get the following..

    Now what is interesting about this is that the text string defined by Microsoft is GMT Standard Time and according to my investigations it definitely adjusts for British Summer Time (which most of the time is what I want!). OK but if you go to Wikipedia you get the following definition of what GMT is :-

    Greenwich Mean Time or GMT is the time displayed by the Shepherd Gate Clock at the Royal Observatory in Greenwich, London. When the sun is at its highest point exactly above the Prime Meridian, it is 12:00 noon at Greenwich.

    GMT is not affected by Daylight Saving Time (DST) clock changes.
    The Greenwich Meridian (Prime Meridian or Longitude Zero degrees) is the reference point for every Time Zone in the world.

    Every 15° longitude represents one hour’s difference in time: (24 x 15 = 360, the degrees of a circle). You can work out the time at every location on earth if you know how many degrees it is east or west of Greenwich.

    GMT is not affected by Daylight Saving Time (DST) clock change -> So obviously that contradicts the definition in SQL Server – fair play to the engineer who set up the sys.table he just wasn’t aware. Probably won’t be changed now but its indicative of the danger there is in dealing with time when you need to be ultra accurate. Users tend to think that dealing with time is straight forward but in reality its not!

    SQL Server – TSQL – Create Trigger that calculates name of day from date

    Nothing earth shattering but really quite useful and often ubiquitious as a requirement I find it useful to have days of the week in tables sometimes and in most tables I will always have default fields of updated and created. I can then use the created field to drive a dayname

    [daysname]  AS (datename(weekday,[T001TimeLog].[created]
    

    And here it is as part of a table’s creation

    CREATE TABLE [dbo].[T001TimeLog](
    	[pkid] [int] IDENTITY(1,1) NOT NULL,
    	[TimeEstimate] [time](7) NULL,
    	[Description] [nvarchar](max) NULL,
    	[updated] [date] NULL,
    	[created] [date] NULL,
    	[daysname]  AS (datename(weekday,[T001TimeLog].[created]))

    And here is the field value on screen and through a User Interface

    SQL Azure – TSQL Script – Address table

    Designed to be used in conjunction with previous post Persons table. Will allow for multiple addresses for individuals. Country field to draw from Country table but I would not foreign key the value to country table identity but reference it to the actual name. Personal naming convention to name foreign keys after the pkid[tablename] I find that works well for me and seems very logical. I have a large system at work which simply names foreign keys fkid can be difficult to find what table it refers to resulted in me thinking hard about a naming convention for foreign keys.
    I like pkid[tableprefix]. I usually leave it at that have expanded below because its a single script and may or may not be used with an actual persons t0001 table.

    /****** Object:  Table [dbo].[t0002address]    Script Date: 01/12/2020 08:12:51 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[t0002address](
    	[pkid] [int] IDENTITY(1,1) NOT NULL,
    	[flatfloorno] [nvarchar](20) NULL,
    	[housebuildingno] [nvarchar](20) NULL,
    	[streetname] [nvarchar](50) NULL,
    	[throughstreet] [nvarchar](50) NULL,
    	[locality] [nvarchar](50) NULL,
    	[towncity] [nvarchar](50) NULL,
    	[districtcountyregionstate] [nvarchar](50) NULL,
    	[country] [nvarchar](50) NULL,
    	[postcode] [nvarchar](50) NULL,
    	[dateentry] [date] NULL,
    	[dateexit] [date] NULL,
    	[currenta] [bit] NULL,
    	[currentset] [date] NULL,
    	[pkidt0001people] [int] NULL,
    	[updated] [date] NULL,
    	[created] [date] NULL,
     CONSTRAINT [PK_t0002address] PRIMARY KEY CLUSTERED 
    (
    	[PKID] ASC
    )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[t0002address] ADD  CONSTRAINT [DF_t0002address_updated]  DEFAULT (getdate()) FOR [updated]
    GO
    
    ALTER TABLE [dbo].[t0002address] ADD  CONSTRAINT [DF_t0002address_created]  DEFAULT (getdate()) FOR [created]
    GO
    

    SQL Azure – TSQL Script – Persons table

    A TSQL script to create a table to act as list of customers , application user list and for lookups for particular lookups.

    The automatestart and automatefinish will allow the automation of entry to an application starting and ending on specific dates.

    /****** Object:  Table [dbo].[t0001people]    Script Date: 29/11/2020 12:39:05 ******/
    SET ANSI_NULLS ON
    GO
     
    SET QUOTED_IDENTIFIER ON
    GO
     
    CREATE TABLE [dbo].[t0001people](
        [pkid] [int] IDENTITY(1,1) NOT NULL,
        [title] [nvarchar](10) NULL,
        [firstname] [nvarchar](50) NULL,
        [middlename] [nvarchar](50) NULL,
        [surname] [nvarchar](50) NULL,
        [gender] [nvarchar] (1) NULL,
        [dateofbirth] [date] NULL,
        [username] [nvarchar](50) NULL,
        [email01] [nvarchar](100) NULL,
        [email02] [nvarchar](100) NULL,
        [mobile01] [nvarchar](20) NULL,
        [mobile01intcode] [nvarchar](4) NULL,
        [mobile02] [nvarchar](20) NULL,
        [mobile02intcode] [nvarchar](4) NULL,
        [landline] [nvarchar](20) NULL,
        [userpassword] [nvarchar](50) NULL,
        [clientlk] [bit] NULL,
        [userlk] [bit] NULL,
        [securitypermission] [nvarchar](50) NULL,
        [automatestart] [date] NULL,
        [automatefinish] [date] NULL,
        [nationalinsuranceno] [nvarchar](20) NULL,
        [pkidt0001mother] [int] NULL,
        [pkidt0001father] [int] NULL,
        [updated] [datetime] NULL,
        [created] [datetime] NULL,
     CONSTRAINT [PK_t0001people] PRIMARY KEY CLUSTERED 
    (
        [pkid] ASC
    )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
     
    ALTER TABLE [dbo].[t0001people] ADD  CONSTRAINT [DF_t0001people_updated]  DEFAULT (getdate()) FOR [updated]
    GO
     
    ALTER TABLE [dbo].[t0001people] ADD  CONSTRAINT [DF_t0001people_created]  DEFAULT (getdate()) FOR [created]
    GO
    

    SQL Azure – TSQL Script – Country File Lookup Table (with data accurate November 2020)

    I’ve written about this in a previous post. I had a client that has customers all over the world and I wanted to align and enforce validation of the correct countrys as stock and product may need to be posted. Its unusual to have an attribute that is both finite and universal between systems – The list is taken from the international recognized ISOcode standard.(November 2020)

    I use this as a lookup field in addresses for people

    /****** Object:  Table [dbo].[t039country]    Script Date: 29/11/2020 11:41:58 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[t039country](
    	[pkid] [int] IDENTITY(1,1) NOT NULL,
    	[isocode] [nvarchar](3) NULL,
    	[country] [nvarchar](50) NULL,
    	[include] [bit] NULL,
     CONSTRAINT [PK_t039country] PRIMARY KEY CLUSTERED 
    (
    	[pkid] ASC
    )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET IDENTITY_INSERT [dbo].[t039country] ON 
    
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (1, N'AD', N'Andorra', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (2, N'AE', N'United Arab Emirates', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (3, N'AF', N'Afghanistan', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (4, N'AG', N'Antigua and Barbuda', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (5, N'AI', N'Anguilla', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (6, N'AL', N'Albania', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (7, N'AM', N'Armenia', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (8, N'AO', N'Angola', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (9, N'AQ', N'Antarctica', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (10, N'AR', N'Argentina', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (11, N'AT', N'Austria', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (12, N'AU', N'Australia', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (13, N'AW', N'Aruba', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (14, N'AX', N'Aland Islands', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (15, N'AZ', N'Azerbaijan', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (16, N'BA', N'Bosnia and Herzegovina', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (17, N'BB', N'Barbados', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (18, N'BD', N'Bangladesh', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (19, N'BE', N'Belgium', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (20, N'BF', N'Burkina Faso', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (21, N'BG', N'Bulgaria', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (22, N'BH', N'Bahrain', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (23, N'BI', N'Burundi', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (24, N'BJ', N'Benin', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (25, N'BL', N'Saint Barts', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (26, N'BM', N'Bermuda', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (27, N'BN', N'Brunei Darussalam', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (28, N'BO', N'Bolivia Plurinational State of', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (29, N'BQ', N'Bonaire Sint Eustatius and Saba', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (30, N'BR', N'Brazil', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (31, N'BS', N'Bahamas', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (32, N'BT', N'Bhutan', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (33, N'BV', N'Bouvet Island', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (34, N'BW', N'Botswana', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (35, N'BY', N'Belarus', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (36, N'BZ', N'Belize', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (37, N'CA', N'Canada', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (38, N'CC', N'Cocos (Keeling) Islands', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (39, N'CD', N'Congo the Democratic Republic of the', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (40, N'CF', N'Central African Republic', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (41, N'CG', N'Congo', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (42, N'CH', N'Switzerland', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (43, N'CI', N'Cote dIvoire', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (44, N'CK', N'Cook Islands', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (45, N'CL', N'Chile', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (46, N'CM', N'Cameroon', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (47, N'CN', N'China', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (48, N'CO', N'Colombia', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (49, N'CR', N'Costa Rica', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (50, N'CU', N'Cuba', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (51, N'CV', N'Cape Verde', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (52, N'CW', N'Curacao', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (53, N'CX', N'Christmas Island', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (54, N'CY', N'Cyprus', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (55, N'CZ', N'Czech Republic', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (56, N'DE', N'Germany', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (57, N'DJ', N'Djibouti', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (58, N'DK', N'Denmark', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (59, N'DM', N'Dominica', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (60, N'DO', N'Dominican Republic', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (61, N'DZ', N'Algeria', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (62, N'EC', N'Ecuador', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (63, N'EE', N'Estonia', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (64, N'EG', N'Egypt', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (65, N'EH', N'Western Sahara', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (66, N'ER', N'Eritrea', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (67, N'ES', N'Spain', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (68, N'ET', N'Ethiopia', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (69, N'FI', N'Finland', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (70, N'FJ', N'Fiji', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (71, N'FK', N'Falkland Islands (Malvinas)', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (72, N'FO', N'Faroe Islands', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (73, N'FR', N'France', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (74, N'GA', N'Gabon', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (75, N'GB', N'United Kingdom', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (76, N'GB', N'United Kingdom Northern Ireland', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (77, N'GD', N'Grenada', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (78, N'GE', N'Georgia', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (79, N'GF', N'French Guiana', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (80, N'GG', N'Guernsey', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (81, N'GH', N'Ghana', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (82, N'GI', N'Gibraltar', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (83, N'GL', N'Greenland', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (84, N'GM', N'Gambia', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (85, N'GN', N'Guinea', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (86, N'GP', N'Guadeloupe', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (87, N'GQ', N'Equatorial Guinea', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (88, N'GR', N'Greece', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (89, N'GS', N'South Georgia and the South Sandwich Islands', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (90, N'GT', N'Guatemala', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (91, N'GW', N'Guinea-Bissau', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (92, N'GY', N'Guyana', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (93, N'HK', N'Hong Kong S.A.R. China', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (94, N'HM', N'Heard Island and McDonald Islands', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (95, N'HN', N'Honduras', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (96, N'HR', N'Croatia', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (97, N'HT', N'Haiti', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (98, N'HU', N'Hungary', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (99, N'ID', N'Indonesia', NULL)
    GO
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (100, N'IC', N'Spain Canary Islands', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (101, N'IE', N'Ireland Republic', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (102, N'IL', N'Israel', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (103, N'IM', N'Isle of Man', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (104, N'IN', N'India', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (105, N'IO', N'British Indian Ocean Territory', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (106, N'IQ', N'Iraq', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (107, N'IR', N'Iran Islamic Republic of', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (108, N'IS', N'Iceland', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (109, N'IT', N'Italy', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (110, N'JE', N'Jersey', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (111, N'JM', N'Jamaica', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (112, N'JO', N'Jordan', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (113, N'JP', N'Japan', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (114, N'KE', N'Kenya', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (115, N'KG', N'Kyrgyzstan', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (116, N'KH', N'Cambodia', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (117, N'KI', N'Kiribati', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (118, N'KM', N'Comoros', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (119, N'KN', N'Saint Kitts and Nevis', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (120, N'KP', N'Korea Democratic Peoples Republic of', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (121, N'KR', N'Korea Republic of', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (122, N'KW', N'Kuwait', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (123, N'KY', N'Cayman Islands', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (124, N'KZ', N'Kazakhstan', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (125, N'LA', N'Lao Peoples Democratic Republic', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (126, N'LB', N'Lebanon', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (127, N'LC', N'Saint Lucia', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (128, N'LI', N'Liechtenstein', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (129, N'LK', N'Sri Lanka', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (130, N'LR', N'Liberia', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (131, N'LS', N'Lesotho', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (132, N'LT', N'Lithuania', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (133, N'LU', N'Luxembourg', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (134, N'LV', N'Latvia', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (135, N'LY', N'Libyan Arab Jamahiriya', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (136, N'MA', N'Morocco', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (137, N'MC', N'Monaco', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (138, N'MD', N'Moldova Republic of', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (139, N'ME', N'Montenegro', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (140, N'MF', N'Saint Martin (French part)', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (141, N'MG', N'Madagascar', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (142, N'MK', N'Macedonia the former Yugoslav Republic of', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (143, N'ML', N'Mali', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (144, N'MM', N'Myanmar', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (145, N'MN', N'Mongolia', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (146, N'MO', N'Macau S.A.R. China', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (147, N'MQ', N'Martinique', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (148, N'MR', N'Mauritania', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (149, N'MS', N'Montserrat', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (150, N'MT', N'Malta', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (151, N'MU', N'Mauritius', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (152, N'MV', N'Maldives', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (153, N'MW', N'Malawi', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (154, N'MX', N'Mexico', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (155, N'MY', N'Malaysia', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (156, N'MZ', N'Mozambique', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (157, N'NA', N'Namibia', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (158, N'NC', N'New Caledonia', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (159, N'NE', N'Niger', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (160, N'NF', N'Norfolk Island', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (161, N'NG', N'Nigeria', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (162, N'NI', N'Nicaragua', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (163, N'NL', N'Netherlands', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (164, N'NO', N'Norway', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (165, N'NP', N'Nepal', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (166, N'NR', N'Nauru', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (167, N'NU', N'Niue', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (168, N'NZ', N'New Zealand', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (169, N'OM', N'Oman', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (170, N'PA', N'Panama', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (171, N'PE', N'Peru', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (172, N'PF', N'French Polynesia', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (173, N'PG', N'Papua New Guinea', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (174, N'PH', N'Philippines', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (175, N'PK', N'Pakistan', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (176, N'PL', N'Poland', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (177, N'PM', N'Saint Pierre and Miquelon', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (178, N'PN', N'Pitcairn', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (179, N'PS', N'Palestine', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (180, N'PT', N'Portugal', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (181, N'PY', N'Paraguay', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (182, N'QA', N'Qatar', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (183, N'RE', N'Reunion', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (184, N'RO', N'Romania', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (185, N'RS', N'Serbia', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (186, N'RU', N'Russian Federation', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (187, N'RW', N'Rwanda', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (188, N'SA', N'Saudi Arabia', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (189, N'SB', N'Solomon Islands', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (190, N'SC', N'Seychelles', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (191, N'SD', N'Sudan', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (192, N'SE', N'Sweden', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (193, N'SG', N'Singapore', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (194, N'SH', N'Saint Helena Ascension and Tristan da Cunha', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (195, N'SI', N'Slovenia', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (196, N'SJ', N'Svalbard and Jan Mayen', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (197, N'SK', N'Slovakia', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (198, N'SL', N'Sierra Leone', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (199, N'SM', N'San Marino', NULL)
    GO
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (200, N'SN', N'Senegal', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (201, N'SO', N'Somalia', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (202, N'SR', N'Suriname', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (203, N'SS', N'South Sudan', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (204, N'ST', N'Sao Tome and Principe', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (205, N'SV', N'El Salvador', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (206, N'SX', N'Sint Maarten (Dutch part)', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (207, N'SY', N'Syrian Arab Republic', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (208, N'SZ', N'Swaziland', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (209, N'TC', N'Turks and Caicos Islands', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (210, N'TD', N'Chad', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (211, N'TF', N'French Southern Territories', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (212, N'TG', N'Togo', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (213, N'TH', N'Thailand', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (214, N'TJ', N'Tajikistan', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (215, N'TK', N'Tokelau', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (216, N'TL', N'Timor-Leste', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (217, N'TM', N'Turkmenistan', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (218, N'TN', N'Tunisia', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (219, N'TO', N'Tonga', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (220, N'TR', N'Turkey', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (221, N'TT', N'Trinidad and Tobago', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (222, N'TV', N'Tuvalu', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (223, N'TW', N'Taiwan', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (224, N'TZ', N'Tanzania United Republic of', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (225, N'UA', N'Ukraine', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (226, N'UG', N'Uganda', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (227, N'US', N'United States', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (228, N'UY', N'Uruguay', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (229, N'UZ', N'Uzbekistan', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (230, N'VA', N'Holy See (Vatican City State)', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (231, N'VC', N'Saint Vincent and the Grenadines', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (232, N'VE', N'Venezuela Bolivarian Republic of', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (233, N'VG', N'Virgin Islands British', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (234, N'VN', N'Vietnam', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (235, N'VU', N'Vanuatu', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (236, N'WF', N'Wallis and Futuna', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (237, N'WS', N'Samoa', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (238, N'YE', N'Yemen', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (239, N'YT', N'Mayotte', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (240, N'ZA', N'South Africa', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (241, N'ZM', N'Zambia', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (242, N'ZW', N'Zimbabwe', NULL)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (243, N'GB', N'United Kingdom Scotland', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (244, N'GB', N'United Kingdom England', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (245, N'GB', N'United Kingdom Wales', 1)
    INSERT [dbo].[t039country] ([pkid], [isocode], [country], [include]) VALUES (246, N'PRI', N'Puerto Rico', 1)
    SET IDENTITY_INSERT [dbo].[t039country] OFF
    

    I’ve just thought I should add a international dialing code field

    SQL Azure – Export Individual Tables Using SQL Server Management Studio

    Connect to SQL Azure through SQL Server Management Studio

    Select the database that contains the table you wish to script

    Right click on the database and select Generate Scripts

    The Generate Scripts dialog should appear choose

    Choose Objects
    Select specific database objects

    Hit Next and set the location of where you wish the file to go

    Review the summary dialog to ensure you have everything correct

    Next you want to decide whether you want to include data or not in the script file

    Hit the advanced button and scroll down to Types of data to script

    If you are wanting data to be included select schema and data

    Hit the Next button and review your configuration

    Hit Next and it will start the process of exporting your table with or without data

    The dialog will update to show status of script generation

    You will now be able to navigate to the script and open it in an editor of your choice to double check that things look ok.

    Here I show a script that includes data – note you can’t see all of the insert information because the script is rather wide but I assure you it is there at the bottom.

    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
    

    SQL Azure – Reset Primary Key Identity/Index to Zero after Deleting all records

    I was faced with a situation where I needed to set up an index for widgets but I wanted those to start at zero. I made some attempts to import information and deleted those records from the table several times and was faced with a situation where users might see the index occasionally. As a result I wanted the index to start from 1 and at least at the start go up to the approximate number of widgets.

    Its a quick search using google to get this but wanted to reset the identity on a blank table.

    In SSMS / navigate to the database and open a new query window

    DBCC CHECKIDENT (dbo.t001table, RESEED,0);
    GO

    MS SQL Azure – Conditional Computed Column Definition Using CASE

    Setting aside for the moment the rights and wrongs of using persisted columns sometimes it is just a great way to add some automation to your database and make it clearer for the user.

    But what if you want to add a conditional computed column for rows based on another value within that row. Here’s something I worked out.

    ALTER TABLE ProjectManagement
    ADD FutureorPast AS CAST
    (
    CASE
    WHEN TargetDate > GetDate() or TargetDate is Null THEN 'FUTURE' WHEN TargetDate <= GetDate() THEN 'PAST'
    ELSE ''
    END as nvarchar(6)
    )
    GO