CODEWITHSUNDEEP
sqlsql-like
Jonas Palačionis
Jonas Palačionis

Reputation: 4842

Check if first word in string pleases condition in 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'

Upvotes: 0

Views: 194

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522441

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.

Upvotes: 3

Related Questions