检查SQL中字符串中的第一个单词是否满足条件

I am trying to filter google-bots from user_agent fields in my database. I am using LIKE %text_I_want_to_check_% and it works fine because so far all of the bot namings are unique, however, AdsBot-Google is not unique in a string, what differs from other user_agents is that AdsBot-Google is the first entry in a string.

Example of user_agent with google-bot:

Mozilla/5.0 (Linux; Android 5.0; SM-G920A) AppleWebKit (KHTML, like Gecko) Chrome Mobile Safari (compatible; AdsBot-Google-Mobile; +http://www.google.com/mobile/adsbot.html)

Exmaple of AdsBot-Google:

AdsBot-Google (+http://www.google.com/adsbot.html)

As you can see both examples contains AdsBot-Google, the only difference is that the second one has it in the first word of a string.

My desired output: SQL CASE which checks if AdsBot-Google is the first text in a string and does not interfere with other cases of AdsBot-Google being in a middle of a string. Something like this:

CASE
WHEN (sessions.user_agent like AdsBot-Google) then 'AdsBot-Google-Mobile-iPhone'
评论
  • rtotam
    rtotam 回复

    The SQL LIKE operator in fact can check for something beginning a string:

    SELECT *
    FROM sessions
    WHERE user_agent LIKE 'AdsBot-Google%';
    

    Note that this would only match records where the user agent field begins with AdsBot-Google; it would not match AdsBot-Google occurring anywhere else.