T-SQL statement to breakdown UserAgent information

I have a table that contains the UserAgent string and a Count of how many times its seen. The T-SQL below is used to give a breakdown of what browsers are seen and how often then are seen.

I'm looking for comments (good or bad) and suggestions (good only).

```
SELECT
Browser,
LEFT(Version, CHARINDEX('.', Version + '.', CHARINDEX('.', Version + '.0') + 1) - 1) AS Version,
SUM(Count) AS Count
FROM
(
SELECT
CASE
WHEN UserAgent LIKE '%Firefox/%' THEN 'Firefox'
WHEN UserAgent LIKE '%Chrome/%' THEN 'Chrome'
WHEN UserAgent LIKE '%MSIE %' THEN 'IE'
WHEN UserAgent LIKE '%MSIE+%' THEN 'IE'
WHEN UserAgent LIKE '%iPhone%' THEN 'iPhone Safari'
WHEN UserAgent LIKE '%iPad%' THEN 'iPad Safari'
WHEN UserAgent LIKE '%Opera%' THEN 'Opera'
WHEN UserAgent LIKE '%BlackBerry%' AND UserAgent LIKE '%Version/%' THEN 'BlackBerry WebKit'
WHEN UserAgent LIKE '%BlackBerry%' THEN 'BlackBerry'
WHEN UserAgent LIKE '%Android%' THEN 'Android'
WHEN UserAgent LIKE '%Safari%' THEN 'Safari'
WHEN UserAgent LIKE '%bot%' THEN 'Bot'
WHEN UserAgent LIKE '%http://%' THEN 'Bot'
WHEN UserAgent LIKE '%www.%' THEN 'Bot'
WHEN UserAgent LIKE '%Wget%' THEN 'Bot'
WHEN UserAgent LIKE '%curl%' THEN 'Bot'
WHEN UserAgent LIKE '%urllib%' THEN 'Bot'
ELSE 'Unknown'
END AS Browser,
CASE
WHEN UserAgent LIKE '%Firefox/%' THEN LEFT(RIGHT(UserAgent + ' ', LEN(UserAgent + ' ') - CHARINDEX('Firefox/', UserAgent + ' ') - 6), CHARINDEX(' ', RIGHT(UserAgent + ' ', LEN(UserAgent + ' ') - CHARINDEX('Firefox/', UserAgent + ' ') - 6)) - 1)
WHEN UserAgent LIKE '%Chrome/%' THEN LEFT(RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Chrome/', UserAgent) - 6), CHARINDEX(' ', RIGHT(UserAgent, LEN(UserAgent) - CHARINDEX('Chrome/', UserAgent) - 6)) - 1)
WHEN UserAgent LIKE '%MSIE %' THEN LEFT(RIGHT(UserAgent + ';', LEN(UserAgent + ';') - CHARINDEX('MSIE ', UserAgent + ';') - 4), CHARINDEX(';', RIGHT(UserAgent + ';', LEN(UserAgent + ';') - CHARINDEX('MSIE ', Use

You can eliminate a few of the conditions by combining them. Your two checks for IE in the "Browser" section can be combined into one WHEN UserAgent LIKE '%MSIE%' since you don't actually report those as different entities. Also, your "Version" section doesn't need explicit checks for all of the bot agents since you just report 0.0 for all of them. You can just let that fall to the ELSE clause.

StackExchange Code Review Q#4657, answer score: 4