Checking for URL values for IDN compatability

Yesterday, Kris posted about a new issue in 8.309 regarding URL controls.  (Infor CRM 8.3.09 Web Client Error with URL Control)  To check for this condition, I put together a quick SQL function that can be used to check url values to see if they would be affected by this bug.

CREATE function [sysdba].[CheckURL](@checkstr varchar(128))
returns bit
begin
   declare @start int = 1; --counter for loop
   declare @occ1 int;
   declare @occ2 int;
   declare @pos int = 1;
   declare @status bit = 0;
   declare @numitems varchar(10);

   select @numitems = len(@checkstr) - len(replace(@checkstr, '.',''))
   while @start <= @numitems 
   begin
      if @start = 1 
      begin 
         set @pos = @start; 
         set @occ2 = 0; 
      end 
      else 
      begin 
         set @pos = @occ1 + 1 
         set @occ2 = @occ1 
      end 
      select @occ1 = CHARINDEX('.', @checkstr, @pos); if @occ1 - @occ2 > 63
      set @status = 1
      set @start = @start + 1;
   End
return @status;
End

To use, simpy reference the function in your SQL script, like:

select accountid, account, webaddress from sysdba.ACCOUNT where sysdba.checkURL(webaddress) = 1

This will return any account records where the account.webaddress would be effected by the URL Issue.