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