ufs_URLSite Scalar-Valued Function

by Bill Dowling 29. July 2010 08:22
Returns Web Site name from a URL line. USE [DB_Name] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ufs_URLSite] ( @URL varchar(2000) ) RETURNS varchar(255) AS BEGIN DECLARE @Result varchar(2000), @SearchFirst varchar(100), @SearchNext varchar(100), @Pos int SET @Result = @URL SET @SearchNext = '/' -- ************** -- Check for http -- ************** SET @SearchFirst = 'http://' SET @Pos = CHARINDEX(@SearchFirst, @Result, 1) -- ********************* -- Nope, check for https -- ********************* IF @Pos = 0 BEGIN SET @SearchFirst = 'https://' SET @Pos = CHARINDEX(@SearchFirst, @Result, 1) END -- ******************* -- Nope, check for www -- ******************* IF @Pos = 0 BEGIN SET @SearchFirst = 'www.' SET @Pos = CHARINDEX(@SearchFirst, @Result, 1) -- ****************************************** -- IF www then fudge with http in front of it -- ****************************************** IF @Pos = 1 BEGIN SET @Result = 'http://' + @Result SET @SearchFirst = 'http://' + @SearchFirst END END -- *************** -- Found something -- *************** IF @Pos = 1 BEGIN SET @Pos = CHARINDEX(@SearchNext, @Result, LEN(@SearchFirst) + @Pos) IF @Pos > 1 -- save up to next slash SET @Result = SUBSTRING(@Result, 1, @Pos - 1) -- ****************** -- Do we have a query -- ****************** SET @Pos = CHARINDEX('?', @Result, LEN(@SearchFirst) + 1) IF @Pos > 1 -- remove the query SET @Result = SUBSTRING(@Result, 1, @Pos - 1) -- ************************ -- Do we have a port number -- ************************ SET @Pos = CHARINDEX(':', @Result, LEN(@SearchFirst) + 1) IF @Pos > 1 -- remove the port SET @Result = SUBSTRING(@Result, 1, @Pos - 1) END -- ************* -- Special Cases -- ************* IF CHARINDEX('http://servername', @Result) > 0 SET @Result = 'http://www.mycompany.com' -- ******************************************************** -- We want to limit the URL LENgth to a specific max LENgth -- ******************************************************** IF LEN(@Result) > 255 SET @Result = SUBSTRING(@Result, 1, 255) RETURN @Result END

Tags:

SQL Coding Examples

ufs_URLEncode Scalar-Valued Function

by Bill Dowling 29. July 2010 08:14
Returns encoded URL line. For example, all spaces are replaced with %20. USE [DB_Name] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ufs_URLEncode](@url varchar(1024)) RETURNS varchar(3072) AS BEGIN -- Peter DeBetta's SQL Programming Blog -- http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-urlencode.ASpx DECLARE @count int, @c char(1), @i int, @urlReturn varchar(3072) SET @count = LEN(@url) SET @i = 1 SET @urlReturn = '' WHILE (@i <= @count) BEGIN SET @c = SUBSTRING(@url, @i, 1) --IF @c LIKE '[A-Za-z0-9()''*-._! ]' IF @c LIKE '[A-Za-z0-9''*-._! ]' BEGIN SET @urlReturn = @urlReturn + @c End Else BEGIN SET @urlReturn = @urlReturn + '%' + SUBSTRING(sys.fn_varbintohexstr(CAST(@c AS varbinary(max))), 3, 2) End SET @i = @i +1 End RETURN @urlReturn END

Tags:

SQL Coding Examples

ufs_URLDecode Scalar-Valued Function

by Bill Dowling 29. July 2010 08:10
Returns decoded URL line. For example, %20 is replaced with a space. USE [DB_Name] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE Function [dbo].[ufs_URLDecode](@url varchar(3072)) RETURNS varchar(3072) AS BEGIN -- Peter DeBetta's SQL Programming Blog -- http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-urldecode.aspx DECLARE @count int, @c char(1), @cenc char(2), @i int, @urlReturn varchar(3072) SET @count = LEN(@url) SET @i = 1 SET @urlReturn = '' WHILE (@i <= @count) BEGIN SET @c = SUBSTRING(@url, @i, 1) IF @c LIKE '[!%]' ESCAPE '!' BEGIN SET @cenc = SUBSTRING(@url, @i + 1, 2) SET @c = CHAR(CASE WHEN SUBSTRING(@cenc, 1, 1) LIKE '[0-9]' THEN CAST(SUBSTRING(@cenc, 1, 1) AS int) ELSE CAST(ASCII(Upper(SUBSTRING(@cenc, 1, 1)))-55 AS int) END * 16 + CASE WHEN SUBSTRING(@cenc, 2, 1) LIKE '[0-9]' THEN CAST(SUBSTRING(@cenc, 2, 1) AS int) ELSE CAST(ASCII(Upper(SUBSTRING(@cenc, 2, 1)))-55 AS int) END) SET @urlReturn = @urlReturn + @c SET @i = @i + 2 END ELSE BEGIN SET @urlReturn = @urlReturn + @c END SET @i = @i +1 END RETURN @urlReturn END

Tags:

SQL Coding Examples

About the author

Bill Dowling has been a VB and .Net programmer for as long as he can remember.

Month List