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.

QR Code with Goo.gl Service

We just created a new QR code service, based on Google URL Shortener tool. Give us a long web page URL, we use Goo.gl to shorten it and then generate the QR code for the shortened URL. In addition, you can add the new URL to the top of the QR code, so it is easy for people without smart-phones to browse the content as well.

Enjoy! Try it now!

QR Code for Pages

We just added a new tool to help businesses use QR code more productively. Businesses often have print-outs that need to be shared with customers, such as restaurant menus and product catalogs. Why not make them accessible on the mobile phones?

Our Page QR service is created to resolve this problem. You can create a web page that can be shared with a QR code. Of course, we will provide you the QR code generated; you can also include the QR code on the web page too. You just need to print the code, make it available in your stores and we will take care of everything else.

Enjoy!

QR Code for Zillow

We are excited to announce that our new product, Zillow QR, is now available. Zillow QR service allows real estate agents and real estate companies to generate QR code for any Zillow property listing. The QR code can be posted on the property “for sale” signs on the lawn. Anyone passing by can scan the code using their smart-phones, get detailed information about the property and contact the agent. We believe it is a great tool for the market.

Try it now!

QR Code for PayPal

We just released a new service to generate QR codes for PayPal buttons. With this service, your customers can quickly purchase products and services with their PayPal accounts using QR codes.

Enjoy! Try it now!

How QR code can help marketers and businesses

A QR code is a special two dimension bar code that can be read by dedicated barcode readers or camera phones.  The data included in a QR code could be contact information, calendar event information, Website URLs or other text values.  As a business owner or marketer, you can print the QR code on any physical surface, such as business cards, tickets, business signs, coffee mugs, t-shirts and etc. People with modern smartphones can capture the QR code with their phone camera and then be given the option to save the contact/event information, go to the web page and etc.

QR code can be used to distribute your contact and event information. It could also be used to promote web pages. Because QR code can be a web URL, you can use the QR code as an entry point and pack most of the information on the actual web page. In addition, you can use web services to make the page action-able, such as allowing customers to add you as a friend on their social networks. QR code can be a powerful tool for marketers and businesses to reach their audiences and customers. In particular, the “coolness” factor could attract young and high-tech people for your products and services. Another big benefit of using the QR code is that when a customer checking a QR code, it is very likely the person is physically close to the code itself, kind of like Yelp and FourSquare combined. This is a perfect moment to market the products and services you want to promote.

QR code has its own challenges as a marketing or promotional tool. First, this channel is quite new, for both businesses and consumers. It is not as straightforward as a plain business sign and you should expect slow start. Second, promotion through QR code is quite different from traditional channels, yet it is not exactly the same as online or social channels either. It is distributed in the physical world and “redeemed” as information text or web pages.  When including QR code in your campaign, you need to consider the both online and offline aspects of your promotion. Third, unless the QR code points to a web page that can be tracked, it is hard to track the effectiveness of the QR codes.

Recommendations:

1. Do use QR code to promote your businesses, even though it is universally understood yet. You can print it on your business cards, post it on the windows and etc.
2. Use QR code as a complimentary tool to promote your products and services, but not as the primary tool. You should still have texts and graphics besides the QR code.
3. When posting QR code, at least, mention what information does the QR code contains and what benefit it offers.
4. Because smartphone cameras are often used for capturing QR codes, when a QR code contains a web page URL, make sure the page is mobile device friendly.
5. Track your responses if possible; however, do not make the user experience too difficult so it defeats the purpose.
6. Educate your audience and customers about the QR code and the benefits of checking the QR code for your products and services.

Please check http://www.uptier.com/ for QR code services we are offering.

Project Started

UpTier started couple weeks ago to help people or businesses to get connected. This blog is created to remember the learnings during the journey. 

To build http://www.uptier.com/, I decide to use Windows Azure. Azure is the Microsoft cloud platform which can scale computing resources as the demand grows. Since I am a BizSpark member, Microsoft offers free Azure for a year, which is a good incentive for me. It is also an interesting new technology to learn. To build the site, I also choose to use the ASP.NET MVC framework, which builds the web site using Model, View, Controller architecture. MVC has a quite learning curve which takes me some time to get used to.

At this time, the deploy process is not very straightforward but overall it is doable. When deploying the code, remember to set the project properties to package all the files. I had a few problems until I chose this option. Also, it is better to deploy to staging first and then switch staging to production, it is better to leave some deployment work to Microsoft :-)