SQL Server detect other browsers from navigator.userAgent

Question

I have a table like below:

create table SiteLog (UserAgent nvarchar(255))
insert into SiteLog values 
('Mozilla/5.0 (iPad; CPU OS 9_3_2 like Mac OS X) AppleWebKit/601.1.46 (KHTML, like Gecko) Version/9.0 Mobile/13F69 Safari/601.1'),
('Mozilla/5.0 (Windows NT 6.3; Win64; x64; rv:60.0) Gecko/20100101 Firefox/60.0'),
('Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/46.0.2486.0 Safari/537.36 Edge/13.10586'),
('Mozilla/5.0 (Windows NT 6.3; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/66.0.3359.181 Safari/537.36'),
('Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.12 Safari/537.36 OPR/14.0.1116.4') 

UserAgent was filled by navigator.userAgent client users. I want to categorize records to 6 categories like below:

1. Firefox
2. Edge
3. IE
4. Chrome
5. Safari
6. Other

I tried to do it like below:

SELECT CASE 
         WHEN Charindex('Firefox', useragent) > 0 THEN 'Firefox' 
         WHEN Charindex('Edge', useragent) > 0 THEN 'Edge' 
         WHEN Charindex('Trident', useragent) > 0 
               OR Charindex('MSIE', useragent) > 0 THEN 'IE' 
         WHEN Charindex('Chrome', useragent) > 0 THEN 'Chrome' 
         WHEN Charindex('Safari', useragent) > 0 THEN 'Safari' 
         ELSE 'Other' 
       END 
FROM   SiteLog 

At first glance, it is very easy, but as you can see in sqlfiddle, the result is not true because the last record has Chrome and it is categorized in Chrome category. I spent too much time to figure out a solution for doing it, but I couldn't.

Edit

I can't use CLR Assembly RegEx Functions

It would be very helpful if someone could explain a solution for this issue.

Answer 1

The technical answer to your issue is that in a SQL case statement, the first response that hits true escapes the rest of the condition checks. You could check the conditions in the most restrictive to least restrictive order and you'll be able to get the output you want.

However, it is not a great solution. User Agent is not reliable or predictable - also there are a whole lot of them https://udger.com/resources/ua-list.

You have another issue that you are doing a search in the middle of the string. This will make this query very expensive and it will be very slow once you hit substantial records.

It would be better to parse the user agent while you are inserting the record so that when you do searches/counts, it can be optimized to use indices.