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.

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!