Tuesday, June 26, 2012

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)
            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)
            text = Replace(text, "www.", "")
        End If
        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
        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)
                text = Replace(text, "www.", "")
            End If
        End If
    End If
    getRootDomain = text
End Function

Friday, January 13, 2012

Excel URL to HOST

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


Wednesday, January 11, 2012

clean text data with regex in MySQL

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,':',' '),'~',' '),'!',' '),'@',' '),'#',' '),'$',' '),'%',' '),'*',' '),'(',' '),')',' '),'_',' '),'+',' '),'{',' '),'}',' '),'[',' '),']',' '),';',' '),' '' ',' '),':',' '),'"',' '),'<',' '),'>',' '),'?',' ')),' ','<>'),'><',''),'<>',' ');

Thursday, January 5, 2012

clean text data with regex in SQL Server

CREATE FUNCTION dbo.tokenizer
    @string varchar(8000)
returns varchar(8000) AS
    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,' ','<>'),'><',''),'<>',' '))))

Thursday, June 9, 2011

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

(@URL varchar(500),
@type nvarchar(50) = 'root')
RETURNS varchar(500)

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'
    IF CHARINDEX('://',@strURL) > 0     return  left(@strURL,charindex('://',@strURL)-1)
    return 'http'
-- 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
    SELECT @path = substring(@strURL,CHARINDEX('/',@strURL)+1, 500)
    SELECT @strURL = LEFT(@strURL,CHARINDEX('/',@strURL)-1)

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'
IF SUBSTRING(@strURL, len(@strURL)-2,1)='.'
        if len(PARSENAME(@strURL,2))<=3
             SELECT @retval = PARSENAME(@strURL,3) + '.' + PARSENAME(@strURL,2) + '.' + PARSENAME(@strURL,1)  
             SELECT @retval = PARSENAME(@strURL,2) + '.' + PARSENAME(@strURL,1)
    SELECT @retval = PARSENAME(@strURL,2) + '.' + PARSENAME(@strURL,1)

RETURN @retval

if @type='path' return @path

--path1, path2, path3...
if left(@type,4)='path'
    select @tmp =@path
    --deal with query strings
    if charindex('?',@tmp)>0
        if @type='pathparam' return substring(@tmp, CHARINDEX('?', @tmp), LEN(@tmp) )
        select @tmp= SUBSTRING(@tmp, 1,  CHARINDEX('?', @tmp)-1)
    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'
            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
            select @level = cast(substring(LOWER(@type),5,10) as int)
            select @tmp = reverse(parsename (@tmp, @level))
            return REPLACE(@tmp, '^^^', '.')

return 'BAD REQUEST'



Tuesday, March 29, 2011

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!

Friday, March 18, 2011

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.