• http://www.techneurons.com/career/
  • experienced programming consultants for hire !!!

    Contact Now

    T-SQL Programming Articles

    KB: T-SQL Function: How to parse UserAgent to extract Browser Name

    WEDNESDAY, MARCH 17, 2010

    As you parse the UserAgent string in the server-side VB.NET or C#.NET code, it is possible to parse it using T-SQL programming. This would be extremely useful when your web application is more SQL Server dependant - having most of the business logic as stored procedures and functions. The only difference here is, as in ASP.NET, the UserAgent string is not accessible directly inside T-SQL context, but you need to pass the string from your ASP.NET calling method as a parameter to your stored procedure query.

    For more information on UserAgent string, you could read the ASP.NET article Snippet: Get Browser Name from UserAgent String.

    What we intend to do here, is to extract the browser name as well as the version. In some cases, like Internet Explorer, this is quite simple - just to look for existence of a regular phrase MSIE 6.0 or MSIE 8.0. But with other browsers, this is not simple because the browser name and version would not be place together, or the version would have more details with minor build number, etc., there may be different version numbers of different sub-components. So for each browser, you have to use a different extraction technique to extract the proper Browser Name and Version.

    The TSQL function would look like this:

           CREATE FUNCTION [dbo].[GetBrowserName](@UserAgent VARCHAR(200))
           RETURNS VARCHAR(30)
           AS
           BEGIN
           DECLARE @BrowserName VARCHAR(30)
           SET @BrowserName = CASE
                  WHEN PATINDEX('%MSIE 8.0%',@UserAgent) > 0
                         THEN 'Internet Explorer 8.0'
                  WHEN PATINDEX('%MSIE 7.0%',@UserAgent) > 0
                         THEN 'Internet Explorer 7.0'
                  WHEN PATINDEX('%MSIE 6.0%',@UserAgent) > 0
                         THEN 'Internet Explorer 6.0'
                  WHEN PATINDEX('%MSIE 5.0%',@UserAgent) > 0
                         THEN 'Internet Explorer 5.0'
                  WHEN PATINDEX('%Firefox%',@UserAgent) > 0
                         THEN 'Mozilla ' + REPLACE(SUBSTRING(@UserAgent,
                               PATINDEX('%Firefox%',@UserAgent), 100),'/', ' ') 
                  WHEN PATINDEX('%Chrome%',@UserAgent) > 0
                         THEN 'Google ' + REPLACE(SUBSTRING(@UserAgent,
                               PATINDEX('%Chrome%',@UserAgent), 
                                      PATINDEX('%Safari%',@UserAgent) -
                                             PATINDEX('%Chrome%',@UserAgent)),'/', ' ') 
                  WHEN PATINDEX('%Safari%',@UserAgent) > 0
                         THEN 'Safari ' + REPLACE(SUBSTRING(@UserAgent,
                               PATINDEX('%Version%',@UserAgent),
                                      PATINDEX('%Safari%',@UserAgent)-
                                             PATINDEX('%Version%',@UserAgent)),'Version/', '')
                  WHEN PATINDEX('%Opera%',@UserAgent) > 0
                         THEN REPLACE(SUBSTRING(@UserAgent,
                               PATINDEX('%Opera%',@UserAgent),
                                      PATINDEX('%(%',@UserAgent)-2),'/', ' ')
                  ELSE
                         'Unknown Browser'
                  END
           RETURN(@BrowserName)
           END

    You can improve this function by adding processing code for more kinds of browsers, or even for bots like GoogleBot, etc.

    ConsultSarath - We provide end to end outsourcing solutions for .net programming requirements- you can hire programmer for hourly rates, for monthly commitments, for short term projects, for long term projects, Contact to know our hourly rates for programmer in India. ConsultSarath - We provide end to end outsourcing solutions for php programming requirements- you can hire programmer for hourly rates, for monthly commitments, for short term projects, for long term projects, Contact to know our hourly rates for programmer in India. ConsultSarath - We provide end to end outsourcing solutions for python programming requirements- you can hire programmer for hourly rates, for monthly commitments, for short term projects, for long term projects, Contact to know our hourly rates for programmer in India.

    Other Popular Articles
    We are experts in Cloud Computing Technologies. We can assist you to build high scalable business applications using Amazon Web Services (Amazon EC2, Amazon S3, Amazon SES, SNS, CloudFront), Windows Azure Platforms - Windows Azure and SQL Server Azure, Google App Engine using Python and Django Framework. We are Expert Programming Consultants available at affordable rates per hour. We work on several technologies - .NET, Python, Google App Engine, PHP, Windows Azure, Amazon Web Services ...