CODEWITHSUNDEEP
sqlgoogle-bigquerywildcard
Suhayb Dar
Suhayb Dar

Reputation: 101

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?

Upvotes: 1

Views: 71

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

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

Upvotes: 1

Related Questions