Category: All

MYSQL / MariaDB – Useful SQL in both creating New Tables and Clarifying Default Parameters

Within an instance to identify the default character sets and collation in all databases.

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

To alter the default character sets and collations for a database in a MySQL or MaraiDB instance.

ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

Creating Table – Template SQL

CREATE TABLE table1 
(col1 INT(10) NOT NULL AUTO_INCREMENT, 
col2 CHAR(30), 
PRIMARY KEY (col1))
ENGINE = INNODB;

And a good source of data types are here
Tech on the Net

There are unconfirmed reposts that queries against TEXT fields are generally 3 times slower than against VARCHAR fields.

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

VARCHAR(MAX)

Is a thing

And if you want to change the engine on a table retrospectively. See OPERATIONS.

And a short note on your choice of collation

From Stack overflow here

Actually, you probably want to use utf8_unicode_ci or utf8_general_ci.

utf8_general_ci sorts by stripping away all accents and sorting as if it were ASCII
utf8_unicode_ci uses the Unicode sort order, so it sorts correctly in more languages
However, if you are only using this to store English text, these shouldn’t differ.

Note further down there is a person that states “be very aware of this problem that can occur using utf8_general_ci

“MySQL will not distinguish between some characters in select statements, if the utf8_general_ci collation is used. This can lead to very nasty bugs – especially for example, where usernames are involved. Depending on the implementation that uses the database tables, this problem could allow malicious users to create a username matching an administrator account.

This problem exposes itself at the very least in early 5.x versions – I’m not sure if this behaviour as changed later.”

And here is some code that allowed me to reset the auto increment index on a table.

ALTER TABLE my_table MODIFY COLUMN pkid INT(10) UNSIGNED;
COMMIT;
ALTER TABLE my_table MODIFY COLUMN pkid INT(10) UNSIGNED AUTO_INCREMENT;
COMMIT;

I have tested it and works with INNODB and MySQL

Another way to do it might be to drop the pkid table and then recreate it again

Using

ALTER TABLE mytable DROP pkid;
ALTER TABLE mytable ADD pkid INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

CPanel – Setting up an additional FTP Account

As previously stated there are a lot of hosted services that come with CPANEL installed. We have already indicated how it is possible to create a subdomain that can be used to host php based sites.
If you are using a design environment you will want to set up an FTP account that can be used to upload your applications too. It is worth just making sure that you are familiar with this.

Firstly log into your account at your host cpanel.

Once configured it should appear in your FTP Accounts section here

If you want to see the FTP parameters for upload hit the related Configure FTP Client

CPANEL only allows you to create against domains and subdomains that already exist so this should be you ready to install software.

SQL Azure – Trying to Alter Computed Column definition

The situation – I have a computed column that I want to alter in SQL Azure

After googling away it turns out you cannot alter a computed column what you have to do is drop it and then re-create it again..

Bit disappointing – I then thought maybe I can just alter the function – Foiled AGAIN – that MIGHT have been possible but for the fact I needed to introduce an additional parameter into the function and that won’t work as I have to alter the parameter definition in the column definition which again is an alteration of the column..

Oh well

You might want to take a minute to compose yourself / make sure you’ve got a backup and then I guess go for it!

ALTER TABLE dbo.t002table1
	DROP COLUMN DistillPeriod
ALTER TABLE dbo.t002table1 ADD DistillPeriod AS ([dbo].[calculatematuration]([t002table1].[dateoffill],getdate(),[t002table1].[disgorgedate]));

Postgres – create a trigger to automatically calculate area of Polygon on edit

This will calculate the area in hectares of a polygon on creation or edit..( referenced here as Before Insert or Update On)

This appears to work for polygon and multi-polygon geometry types I have had a discussion with a colleague and he has indicated that the type of project may affect the accuracy of the measurement. Certainly there is a straight division in there so it would probably be possible to alter the division figure by empircally testing to get it as accurate as possible. Apparently with some popular projections the further north you are the less accurate the resultant figure. (Just something to bear in mind).

CREATE OR REPLACE FUNCTION calc_area() 
RETURNS trigger AS $BODY$ 
BEGIN NEW.grossarea := ROUND((st_area(NEW.geom)/10000)::numeric,2); 
RETURN NEW; 
END; 
$BODY$ LANGUAGE plpgsql;

What I found interesting about this was the field name is defined in the function and called from the trigger. There is possibly a better way of writing this to define the field in the trigger itself..

And the trigger that goes on the table

CREATE TRIGGER area_calculate BEFORE INSERT OR UPDATE ON public.t001landparcels FOR EACH ROW EXECUTE PROCEDURE calc_area();

CPANEL setting up ALLOW and Deny on IP addresses / forcing HTTPS

Two changes to the .htaccess that are easy to implement and will improve the security of PHP websites.

If you have subdomains that you would like to restrict access to and they are running a PHP site. You can use the .htaccess file to allow or deny access.

A very uncomplicated way is if you have a particular site that you would like to ringfence why not just reduce access to it to one location… Your home..

<RequireAny>
require ip x.x.x.x
require ip y.y.y.y
</RequireAny>

You can also use this to allow or block entire countries

Block a country from your site

And if you would like to find out the IP address range required to block you can find that here.

IP2location.com

The nice thing about this is that it works on subdomains. Using the CPANEL file manager just go into the subdomain find the htaccess file and block accordingly.

And

Force all of your pages to use HTTPS. To do this you will need to modify your .htaccess file (or create one if it doesn’t exist)

Using the Code Editor in the file manager, add these lines to the beginning of the .htaccess file

RewriteEngine On
RewriteCond %{HTTPS} off
RewriteRule ^(.*)$ https://%{HTTP_HOST}%{REQUEST_URI} [L,R=301]

Installation of Nubuilder through CPANEL

Background – I wrote sometime ago about an open source low code project called nubuilder originally started by a Steven Copely – it has been steadily and consistently developed over the years and Steven has since shared development with some other talented individuals. I took another look at it recently to see whether  I could find a way of installing and playing about with it on my existing web hosting provider…

and…

If you have a webhost that allows you access to a CPANEL client to configure your hosting environment then it is highly likely that you can set up a nubuilder low code environment. If you have a webhost that allows access to a CPANEL account AND allows you unlimited sub domains and unlimited storage then it is highly likely that you will be able to create a nubuilder low code environment for zero cost!!! 

A quick search online found that the following offer CPANEL / unlimited subdomains / unlimited MYSQL

A2Hosting

WebhostingHub (which are a subsidiary of InMotion)

SiteGround

There is usually a limit on the size of an individual MYSQL database but it is so high as to not be an issue.

Configuration Instructions:

This post was adapted from Steven Copleys video which can be found here..

Installing on Bluehost

First below I have blanked out my cpanel username variable with either a grey circular box or in code I’ve replaced it with an X. This variable is implemented by CPANEL software to allow resellers to ensure that thousands of users on the same servers do not accidentally duplicate directory and filenames. The username can only be accessed with an additional password but it is not generally available to anyone but the host admin and as such is additional security. It will be used in many of the default settings when creating things like databases and directory names.

Firstly Log into your providers CPANEL

Look to the Domains section and within it there should be a Subdomains icon.

and select the Subdomains icon

Create a new subdomain

Here is the Subdomain field I have typed nbexample – the document root will automatically be completed

Hit the Create button

Next we create a database

go to MySQLDatabases within the Databases menu

Now we create a New User

Here I do the same

username will be X_nb4exampledbuser

password

BlueSkyIsEverywhereToday2020

Hit the create user and Go Back and add the user to the database

On hitting add you will be asked to select the privileges that the user has over the database just indicate that you would like to allow them all privileges

Then hit the make changes button and you can if you want then check the MySQL databases and check that the database exists and that the user is there.

Next we go to the Nbuilder Github site and download the master file but zipped

Nubuilder 4.5 on GitHub

And select the Dowload ZIP option from the Code drop down list.

This will download a master file to your dowload folder – In your browser window in the bottom left you will see the file like this

You now need to go back to the main CPANEL hub and look for section marked Files and look for the File Manager option. In my CPANEL it looks it is a red icon.

The subdomain will be created as a directory within your root home directory and in my example looks like this. You will be transferring the master zip file into this directory so you want to select it.

Next hit the Upload button and navigate to find

nubuilder-4.5-master.zip

And load it into your base directory. It should look something like this now

Next you want to extract it..

It will then ask where you wish to extract it to

I will normally not put anything in here as it creates its own directory.

There will be a short delay after which you will be presented with a dialog that shows the outcome of the extraction process.

You should now see a new directory in which in my case is called nuBuilder-4.5-master.

I don’t like hypens dots and capitals or special characters in directory names. So

  • I rename it to nubuilder45
  • Convert to all lower case
  • Delete the old zip file

Next we want to go into the nuBuilder45 file and open up the nuconfig.php file.

This is where we will link allow the nubuilder php  to link to the created mysql database.

Right mouse click and select edit.

And find the following lines

Now remember from the start our database name / user and password are as follows.

  • Database : X_nb4exampledb
  • Username : X_nb4exampledbuser
  • Password : BlueSkyIsEverywhereToday2020

And hit the changes..

Next we want to create a very simple index.php file – alter the path to suit your subdomain / domain and directory where you extracted the nubuilder master file to.

<!DOCTYPE html>
<html>
<body>
<script type="text/javascript">
window.location.replace("https://nbexample.cloudydatablog.net/nubuilder45")
</script>
</body>
</html>
</body>
</html>

It may well look like this

This simple index.php file is placed in the root of the new subdomain you created

Now you should be able to go to the new subdomain in any browser on the planet and …

 

Congratulations you have successfully configured a default instance of nubuilder – your journey to creating low code online databases can now begin..

PS the default credentials are

Username : globeadmin

Password : nu

Please change these immediately in the nuconfig.php file when you get a chance.

For more information on starting to design and develop with nubuilder please follow the link below

link

 

 

 

 

 

 

 

 

CloudyDataBlog.net – New Domain

Hey everyone – I transferred over to the domain cloudydatablog.net – the old name had been a bit of a throw away idea that ended up lasting for 6 years. Fast forward to January 2021 and thought it would be good to get something more representative of the content.

I’m sitting on WordPress 2016 theme I experiment with other themes every now then but I prefer as standard as possible. There are a lot of posts here and a lot of themes can’t handle the amount of stuff. I’ve tried Ocean WP which I use for smaller sites as I prefer the layout but it throws a fatal error with this site.

SQL Azure – a short note on BAK and Bacpac files

I note that for local SQL Express and I believe enterprise SQL Server there is the additional option of creating a Backup in SQL Express. I also note that from my reading Baks are considered preferable to bacpac files because they have enforced ACID compliance and that for large databases that are constantly being used they are recommended to bacpac files. SQL Azure doesn’t allow BAK file backups through SSMS from what I can see so if this is an issue for you consider temporarily disconnecting front ends from the database while a bacpac is conducted. If you need a bak file for some reason you can attach locally to a SQL Server instance and from there take a bak file.

Something to be aware of..

See this link for further information
Blobeater blog post

ASP Net Runner – Run a SQL Azure Stored Procedure from the web client

Firstly create the stored procedure you wish to run in Azure SQL

Here’s a simple procedure which triggers and update query that inserts records into table 34 from a view based on a flag in the table saying transform is 1 it then runs another query to set the transform field to 0 to prevent the insert happening again the next time.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[transformemail]
AS 
SET NOCOUNT ON;
INSERT INTO t034 (pkid,body1,eplanno,money4,trandate,payref,wlcref,tranid,email,updated)
SELECT pkid,body1,eplanno,money4,trandate,payref,wlcref,tranid,email,updated 
FROM v031;
UPDATE t032email
SET transform = 0
WHERE t032email.transform = 1;
GO

Firstly navigate to the form you wish to put the code into and insert a button into that form.

Next using your mouse select the button and on the right hit the button marked Edit button code

This reveals the Button Properties window dialogue

Please note there are three tabs at the top here marked

Client Before Server and Client After – we will be placing code in all of them so take note

In the Client Before tab I enter.

params["txt"] = "Transformation";
ajax.setMessage("Sending request to server...");
 // Uncomment the following line to prevent execution of "Server" and "Client After" events.
 // return false;

In the Server tab I enter

tDAL.CustomQuery ("EXEC dbo.transformemail");
result["txt"] = parameters["txt"].ToString() + " complete";

And in the Client after tab I enter

// Put your code here.
var message = result["txt"] + " !!!";
ajax.setMessage(message);

Now build the project and when you hit the button on the web client your procedure will be run on the server.

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.

WordPress Useful Plugins

I am increasingly using WordPress and as I have used it more and more I am finding difficulties in managing posts and in particular knowledge base sites. The following is a random list of plugins that I have been using and that help me on a day to day basis.

Dashboard word count – creates a really nice summary of all the posts that you have made.

Syntax Highlighter Evolved – place nicely coloured code boxes in posts
Link

I used the above code block plugin but now prefer the below one it has a copy option which I like but does not work well with the classic editor
Code Block Pro – Beautiful Syntax Highlighting

Post Types Order – Sort posts in any order rather than descending post date order
Link

Print Friendly – pdf individual posts
Link

Password Protected – protect the whole site
Link

There is also something called SeedProd that does have a free version but I haven’t tested this one..
Link

Broken Link Checker – Very useful for blogs where you have hundreds of links – A really nice automated script that goes through and checks all of you links.. On this site I had 511 unique links and it went through and checked them all in under an hour.
Link

Login Logo – this is an important one if you want your site to look a lot better than the standard.

Link

WordPress Login URL change it .  A very useful and simple security improvement.

Link

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

Postgres Command Line : psql : Create a switch to determine whether an Arc has a clockwise or anti-clockwise direction

This is a post focused around Network distance measurement using POSTGIS and routing plugins.. This discusses the use of Ordnance survey Road Network information and its preparation in using it for routing.

The Ordnance Survey open source road network layer includes roundabouts that have an attribute value of roundabout. Great but looking at them closely some of the constituent arcs are digitised in a clockwise direction while others are digitised in an anti-clockwise direction. When using dijkstra routing with weighting to prevent incorrect pathing it is necessary to ensure that networks are weighted in the correct fashion. Directional weighting only works if you know the direction and you weight appropriately. For use with directional routing in the UK roundabouts any directional weighting should prevent travel in anticlockwise direction. ST_reverse will correct incorrect direction BUT the Ordnance survey layer seems to have no attribute that consistently indicates whether an arc on a roundabout has or has not been digitised correctly. Marking lines with direction and viewing them we see the random nature of many arcs on roundabouts.

Here is Sheriff Hall Roundabout on Edinburgh City Bypass

Here is Straiton roundabout just north of the bypass

and finally Hermiston Gate roundabout again following the theme on Edinburgh city bypass

It got me thinking was there a way to determine whether arcs on roundabouts could be determined to be clockwise or anti-clockwise?

Having thought about it in my head quite a bit I determined that it probably would be possible if we knew three points and could create some kind of virtual graph with the start point being at 6 and a finish at 12 with mid points at 9 if travelling in a clockwise position and 3 if travelling in an anti-clockwise position.

I had a look around and the following post seemed to tally with the idea of three points and positive and negative relating to clockwise or anticlockwise.

Maths to determine direction of an arc

Having looked at this I set about working through the problem in Excel to see if I could get consistent results.

Firstly I created a set of directions North West South and East and placed some coordinates that could be used in calculations.

I then went forward and tested whether I could identify the direction of various arcs from these coordinates in excel using the formula identified on Stack Exchange.

Here I replaced a,b,c with b Beginning, m Middle and f Finish

And I decided to work through manually in excel to ensure that I had the maths correct.

and further testing figures

So firstly I create a separate table that just shows the roundabouts

CREATE TABLE t001roundaboutsntroadlink as select id pkidt001, st_linemerge(geom) as geom from ntroadlink where formofway = 'Roundabout';

In the above I use st_linemerge to ensure that all geometry is linestring as this is necessary to use the st_startpoint and st_endpoint postgis functions.

Next I added the the required variables from stack overflow algebra to the line table

ALTER TABLE t001roundaboutsntroadlink add column bx float(8),
Add column by float(8),
Add column mx float(8),
Add column my float(8),
Add column fx float(8),
Add column fy float(8),
Add column ux float(8),
Add column uy float(8),
Add column vx float(8),
Add column vy float(8),
Add column uxtimesvy float(8),
Add column uytimesvx float(8),
Add column uxv float(8);

Next I needed to identify a b beginning, m middle and f finish point for each line that I wanted to test.

b points (beginning)

CREATE TABLE t002bpoints AS SELECT pkidt001 as pkidt001,st_startpoint(geom) as geom, st_x(st_startpoint(geom)) as bx, st_y(st_startpoint(geom)) as by from t001roundaboutsntroadlink;

m points (middle)

CREATE TABLE t002mpoints AS SELECT pkidt001 as pkidt001,st_lineinterpolatepoint(geom,0.5) as geom, st_x(st_lineinterpolatepoint(geom,0.5)) as mx, st_y(st_lineinterpolatepoint(geom,0.5)) as my from t001roundaboutsntroadlink;

f points (finish)

CREATE TABLE t002fpoints AS SELECT pkidt001 as pkidt001,st_endpoint(geom) as geom, st_x(st_endpoint(geom)) as fx, st_y(st_endpoint(geom)) as fy from t001roundaboutsntroadlink;

It was then a case of simple update queries to complete the table

update t001roundaboutsntroadlink set bx = st_x(st_startpoint(geom));
update t001roundaboutsntroadlink set by = st_y(st_startpoint(geom));
update t001roundaboutsntroadlink set mx = st_x(st_lineinterpolatepoint(geom,0.5));
update t001roundaboutsntroadlink set my = st_y(st_lineinterpolatepoint(geom,0.5));
update t001roundaboutsntroadlink set fx = st_x(st_endpoint(geom));
update t001roundaboutsntroadlink set fy = st_y(st_endpoint(geom));
update t001roundaboutsntroadlink set ux=mx-bx;
update t001roundaboutsntroadlink set uy=my-by;
update t001roundaboutsntroadlink set vx=fx-mx;
update t001roundaboutsntroadlink set vy=fy-my;
update t001roundaboutsntroadlink set uxtimesvy = ux*vy;
update t001roundaboutsntroadlink set uytimesvx= uy*vx;
update t001roundaboutsntroadlink set uxv = uxtimesvy-uytimesvx;

Labelling up the roundabouts Hermiston Gate now looks like

And Sheriff Hall Roundabout now looks like this

Compared with a correctly directed roundabout

CREATE TABLE t001roundaboutsntroadlinkcorrected AS TABLE t001roundaboutsntroadlink;

And now correct the items display as previous and see what we see.

UPDATE t001roundaboutsntroadlinkcorrected set geom = st_reverse(geom) where uxv > 0;

Sheriff hall roundabout now

and some proof that reasonable number of lines were updated.

Which is an indication that all roundabouts arcs have been corrected properly

But a zero uxv value indicates a straight line.

It should however be possible to match starts with finishes for overlying points and where a line has 0 value of uxv and its ends and finishes are not matched with adjacent opposites create a switch to reverse the direction of all lines that are incorrect compared to their neighbours thus only correcting incorrect directions. Haven’t done that in this case.