How would i use a wildcard for a specific string in SQL

I have a string which I need to select in a CASE statement:

LLF_RET_mob

However, when I use the following string in my LIKE statement in SQL it returns null:

%LLF\_RET_\_mob%

What would be correct syntax to use for this wildcard?

Answer

You should use '%LLF\_RET\_mob%' as in example below

#standardSQL
WITH `project.dataset.table` AS (
  select 1 id, 'aaaLLF_RET_mobbbb' col union all
  select 2, 'aaaLLFRETmobbbb'
)
SELECT *
FROM `project.dataset.table`
WHERE col LIKE '%LLF\_RET\_mob%'

Or you can use regexp as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, 'aaaLLF_RET_mobbbb' col UNION ALL
  SELECT 2, 'aaaLLFRETmobbbb'
)
SELECT *
FROM `project.dataset.table`
WHERE REGEXP_CONTAINS(col, r'LLF_RET_mob')

Obviously you can use both LIKE or REGEXP_CONTAINS as a condition in CASE statement