VBA, URL to Root Domain

Public Function ReverseString(text As String)
    ReverseString = StrReverse(text)
End Function

Public Function getRootDomain(text As String)
    Dim i
   
    ' Remove protocol
    text = Replace(text, "http://", "")
    text = Replace(text, "https://", "")
    text = Replace(text, "ftp://", "")
   
    ' Remove trailing / and path
    i = InStr(text, "/")
    If (i > 0) Then
        text = Left(text, InStr(text, "/") - 1)
    End If
   
    If Left(Right(text, 5), 1) = "." Then     '  info TLD
   
        If InStr(6, ReverseString(text), ".") > 0 Then
            text = Right(text, InStr(6, ReverseString(text), ".") - 1)
        Else
            text = Replace(text, "www.", "")
        End If
   
    ElseIf Left(Right(text, 4), 1) = "." Then   '.xxx TLD
   
        If InStr(5, ReverseString(text), ".") > 0 Then
            text = Right(text, InStr(5, ReverseString(text), ".") - 1)
        Else
            text = Replace(text, "www.", "")
        End If
   
    Else
        'xxx.TLD.uk
        If Left(Right(text, 7), 1) = "." Then
            If InStr(8, ReverseString(text), ".") > 0 Then
                text = Right(text, InStr(8, ReverseString(text), ".") - 1)
            Else   'we don't not handle http://www.wp.pl/ case well
                text = Replace(text, "www.", "")
            End If
   
        'xxx.xx.uk
        ElseIf Left(Right(text, 6), 1) = "." Then
            If InStr(7, ReverseString(text), ".") > 0 Then
                text = Right(text, InStr(7, ReverseString(text), ".") - 1)
            Else   'we don't not handle http://www.wp.pl/ case well
                text = Replace(text, "www.", "")
            End If
   
        Else  'xxxx.uk
       
            If InStr(4, ReverseString(text), ".") > 0 Then
                text = Right(text, InStr(4, ReverseString(text), ".") - 1)
            Else
                text = Replace(text, "www.", "")
            End If
           
   
        End If
      
   
    End If
   
   
    getRootDomain = text
End Function

Excel URL to HOST

Excel URL to HOST. If the Host is of "www", it will be reduced to root domain.

=IF(LEFT(LEFT(SUBSTITUTE(SUBSTITUTE(A1,"https:","http:"),"http://",""),FIND("/",SUBSTITUTE(SUBSTITUTE(A1,"https:","http:"),"http://","")&"/")-1),4)="www.",MID(LEFT(SUBSTITUTE(SUBSTITUTE(A1,"https:","http:"),"http://",""),FIND("/",SUBSTITUTE(SUBSTITUTE(A1,"https:","http:"),"http://","")&"/")-1),5,256),LEFT(SUBSTITUTE(SUBSTITUTE(A1,"https:","http:"),"http://",""),FIND("/",SUBSTITUTE(SUBSTITUTE(A1,"https:","http:"),"http://","")&"/")-1))

clean text data with regex in MySQL

CREATE FUNCTION tokenizer(x VARCHAR(255))
RETURNS VARCHAR(255) DETERMINISTIC
RETURN replace(replace(replace(lower(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(x,':',' '),'~',' '),'!',' '),'@',' '),'#',' '),'$',' '),'%',' '),'*',' '),'(',' '),')',' '),'_',' '),'+',' '),'{',' '),'}',' '),'[',' '),']',' '),';',' '),' '' ',' '),':',' '),'"',' '),'<',' '),'>',' '),'?',' ')),' ','<>'),'><',''),'<>',' ');

clean text data with regex in SQL Server

CREATE FUNCTION dbo.tokenizer
(
    @string varchar(8000)
)
returns varchar(8000) AS
BEGIN
    DECLARE @handle int, @result varchar(8000)


    exec sp_oacreate 'vbscript.regexp', @handle output
    exec sp_oasetproperty @handle, 'pattern', '([{}\(\)\^$&._%#!@=<>:;,~`\’\*\?\/\+\|\[\\\\]|\]|\-)'
    exec sp_oasetproperty @handle, 'global', 'true'
    exec sp_oasetproperty @handle, 'ignorecase', 0
    exec sp_oamethod @handle, 'replace', @result output, @string, ' '
    exec sp_oadestroy @handle

    RETURN rtrim(ltrim(lower(replace(replace(replace(@result,' ','<>'),'><',''),'<>',' '))))
END

MS SQL, Parse URL procedure

Very often, I need to parse URLs into different parts, here is a quick funciton I wrote to do that. Obviously, you can make it more robust if you want.

-- Right now, it support the following parsing of a URL
-- root, host, protocol, path, pathn (where n is a number indicating the nth parts of the path), pathcoun, pathpara, port

CREATE FUNCTION [dbo].[parseURL]
(@URL varchar(500),
@type nvarchar(50) = 'root')
RETURNS varchar(500)
AS
BEGIN

declare @retval nvarchar(500)
declare @strURL nvarchar(500)
declare @path nvarchar(500)
declare @level int
declare @port nvarchar(100) ='80'
declare @tmp nvarchar(500)
declare @tmp1 nvarchar(500)

select @strURL = LOWER(@URL)

-- Fix some bad URLs in the system
IF CHARINDEX('http://http:.',@strURL) > 0     select @strURL = replace(@strURL,'http://http:.','http://')
IF @strURL='http://www.' or @strURL='http://www'     select @strURL =NULL

select @type = LOWER(@type)

if @type='protocol'
begin
    IF CHARINDEX('://',@strURL) > 0     return  left(@strURL,charindex('://',@strURL)-1)
    return 'http'
end
  
-- Does not handle URLs with user name and password
IF CHARINDEX('://',@strURL) > 0            SELECT @strURL = substring(@strURL,charindex('://',@strURL)+3, 500)

-- Remove everything after "/" if one exists
IF CHARINDEX('/',@strURL) > 0
begin
    SELECT @path = substring(@strURL,CHARINDEX('/',@strURL)+1, 500)
    SELECT @strURL = LEFT(@strURL,CHARINDEX('/',@strURL)-1)
end

if @type='host' return @strURL
IF CHARINDEX(':',@strURL) > 0 select @port = SUBSTRING(@strURL,CHARINDEX(':',@strURL)+1, 100)
if @type='port' return @port


-- handle cases like .co.uk,
-- 81-17-241-170.webhostingireland.ie


if @type='root'
begin
IF SUBSTRING(@strURL, len(@strURL)-2,1)='.'
    begin
        if len(PARSENAME(@strURL,2))<=3
             SELECT @retval = PARSENAME(@strURL,3) + '.' + PARSENAME(@strURL,2) + '.' + PARSENAME(@strURL,1)  
        else
             SELECT @retval = PARSENAME(@strURL,2) + '.' + PARSENAME(@strURL,1)
    end
ELSE
    SELECT @retval = PARSENAME(@strURL,2) + '.' + PARSENAME(@strURL,1)

RETURN @retval
end

if @type='path' return @path

--path1, path2, path3...
if left(@type,4)='path'
begin
    select @tmp =@path
  
    --deal with query strings
    if charindex('?',@tmp)>0
    begin
        if @type='pathparam' return substring(@tmp, CHARINDEX('?', @tmp), LEN(@tmp) )
        select @tmp= SUBSTRING(@tmp, 1,  CHARINDEX('?', @tmp)-1)
    end
    else if @type='pathparam' return ''
  
    -- remove leading and trailing /
    if LEFT(@tmp,1)='/' select @tmp = substring(@tmp, 2, LEN(@tmp) )
    if right(@tmp,1)='/' select @tmp = substring(@tmp, 1, LEN(@tmp)-1)
  
    select @tmp = REPLACE(@tmp, '.','^^^')      
    select @tmp = REPLACE(@tmp, '/','.')
    select @tmp = REVERSE(@tmp)
  
    -- try to count the number of parts in a path
    if @type='pathcount'
        begin
            if isnull(PARSENAME(@tmp,1),'NULL')='NULL' return 0
            if isnull(PARSENAME(@tmp,2),'NULL')='NULL' return 1
            if isnull(PARSENAME(@tmp,3),'NULL')='NULL' return 2
            if isnull(PARSENAME(@tmp,4),'NULL')='NULL' return 3
            if isnull(PARSENAME(@tmp,5),'NULL')='NULL' return 4
            if isnull(PARSENAME(@tmp,6),'NULL')='NULL' return 5
            if isnull(PARSENAME(@tmp,7),'NULL')='NULL' return 6
            if isnull(PARSENAME(@tmp,8),'NULL')='NULL' return 7
            if isnull(PARSENAME(@tmp,9),'NULL')='NULL' return 8
            if isnull(PARSENAME(@tmp,10),'NULL')='NULL' return 9
        end
    else
        begin
            select @level = cast(substring(LOWER(@type),5,10) as int)
            select @tmp = reverse(parsename (@tmp, @level))
            return REPLACE(@tmp, '^^^', '.')
        end
end


return 'BAD REQUEST'

END

GO

QR Code that supports limited use or expiration date

We just created a new QR code service that can be used to redirect users to a web page, with a twist. You can set a limit or an expiration date for the redirect. Once the limit or expiration date is reached, we will redirect the future user to the URLs you specified when setting the limit or expiration date.

Enjoy! Try it now!

QR code for Restaurants

Hi, All:

I have developed a QR code application for restaurants. QR codes are 2-dimensional bar codes that can be read by smart phones. The code contains a web page URL users can browse with their smart phones. The web page could also contain restaurant phone number that can be called with a simple click.

I think it is perfect for take-out menus. The main advantages are:

1. You can save money print outs. As more people using mobile webs, there is less need for paper based take-out menus.
2. Web pages with a “click-to-call” function will be very convenient to all dinners on the go.
3. With QR code, customers don’t need to manually type the web URL into their phones. They just scan it to browse the content, with the option to bookmark it for future use.
4. If you already have a web page for takeout menu, you can use our free Goo.gl service to generate QR code for the menu URL page.
5. You can post the QR code in your restaurants so that customers can scan it and bookmark the web page.

Our service is at http://www.uptier.com/Home/PageQR, where you can create the take-out menu using QR code, we will host it for $10 a year with a one-month free trial. I have created a simple example take out menu with the QR code (see top) that you can test with your smart phones. Some restaurants might already have web pages, but are they mobile-device friendly?

As a new service promotion, we will create mobile friendly takeout menus with QR code technology for you. You just need to give us your existing web page URL or menu list. With only $10 per year, you can enjoy the service with minimum efforts.

Thank you. Please email me at uptier@gmail.com if you have any questions.