The Problem

Sometimes analytics datasets include the raw user-agent string but not a clean device category. That creates a reporting problem if you want to compare mobile and non-mobile journeys.

Full user-agent parsing can be heavy. It may require external libraries, lookup tables or extra processing. For many service analytics use cases, a simpler SQL regex approach is good enough.

The Simple Version

The query below creates a three-way device category directly from the user-agent string.
SQL
CASE
  WHEN UAString LIKE '%iPad%' THEN 'Tablet'
  WHEN regexp_like(
    UAString,
    '.*\s[A-Z]{3}-[0-9a-zA-Z]+|Mozilla\/4\.0|Opera mini|SamsungBrowser|CFNetwork|Nokia|Nexus|Phone|Mobi|CPH1823|IN2023|KB2003|M2007J3SG|M2012K11AG|moto g|P40|Pixel [0-9]{1}|Q10|Redmi|SM-(?:A|G|J|F|S)[0-9a-zA-Z]+|VOG-(?:A|L)[0-9a-zA-Z]+.*'
  ) THEN 'Mobile'
  ELSE 'Desktop / Other'
END AS Device_Category
Design principle: this does not try to fully parse the user agent. It simply checks whether the string contains known mobile or tablet indicators.

Why This Is Useful

For dashboarding, you often do not need a full browser and device breakdown. You may only need to know whether a user was likely on a mobile device.
  • Are mobile users giving lower CSAT scores?
  • Are mobile users more likely to hit session-expired pages?
  • Are form errors more common on mobile?
  • Does a journey perform worse on mobile than desktop?

Breaking Down the Regex

Phone|Mobi
Generic mobile indicators that appear in many phone user-agent strings.
Opera mini|SamsungBrowser
Mobile browser signatures.
Nokia|Nexus|Pixel|Redmi
Common manufacturer or device family indicators.
SM-(?:A|G|J|F|S)
Samsung model families such as SM-A, SM-G, SM-J, SM-F and SM-S.
VOG-(?:A|L)
Huawei-style model patterns.

Why Check iPad First?

The tablet condition comes first:
SQL
WHEN UAString LIKE '%iPad%' THEN 'Tablet'
The order matters. If tablets are checked after the mobile condition, some tablets may already have been classified as mobile.

Reducing Query Load

Regex can be relatively expensive over very large event tables. A simple optimisation is to filter the dataset first, then classify the user agent.
SQL
WHERE tags.path NOT LIKE '%/assets/%'
AND tags.path NOT LIKE '%/images/%'
AND tags.path NOT LIKE '%/fonts/%'
AND detail.status_code = '200'
This removes irrelevant asset, image and font requests before running device classification.

Classify Once, Reuse Later

If the same user-agent strings appear repeatedly, classify the distinct user-agent strings once and join them back to page views.
SQL
WITH ua_classification AS (
  SELECT DISTINCT
    UAString,
    CASE
      WHEN UAString LIKE '%iPad%' THEN 'Tablet'
      WHEN regexp_like(UAString, 'Phone|Mobi|SamsungBrowser|Pixel|Redmi|SM-(?:A|G|J|F|S)[0-9a-zA-Z]+') THEN 'Mobile'
      ELSE 'Desktop / Other'
    END AS Device_Category
  FROM source_table
)
SELECT
  p.date,
  p.session_id,
  p.page_path,
  u.Device_Category
FROM page_views p
LEFT JOIN ua_classification u
ON p.UAString = u.UAString

Limitations

This approach is not perfect. It may misclassify unusual Android devices, embedded web views, tablets that do not identify clearly, bots using mobile-like strings, and future devices not included in the regex.
Treat this as a practical reporting classifier, not a complete device intelligence system. Review it periodically against real user-agent samples.

Conclusion

Device categorisation does not always need a full parsing library. For operational analytics, a lightweight regex can provide a useful mobile, tablet and desktop split directly in SQL.