SQL find '%' between %s-CodePudding

I need to find (exclude in fact) any results that contain '%' sign, wherever in a string field. That would mean ... WHERE string LIKE '%%%'. Googling about escaping gave me the following ideas. The first throws syntax error, the second returns rows but there are records actually contain '%'.

1st:

SELECT * FROM table
WHERE string NOT LIKE '%!%%' ESCAPE '!'
///tried with different escape characters

2nd:

SELECT * FROM table
WHERE string NOT LIKE '%[%]%'

Trying on GCP BigQuery.

CodePudding user response：

Try:

SELECT * 
FROM table
WHERE string NOT LIKE '%!%%' {ESCAPE '!'}

With curly braces as shown in microsoft sql server docs

CodePudding user response：

Or also:

WITH indata(s) AS (
            SELECT 'not excluded'
  UNION ALL SELECT '%excluded'
  UNION ALL SELECT 'Ex%cluded'
  UNION ALL SELECT 'Excluded%'
)
SELECT * FROM indata WHERE INSTR(s,'%') = 0;
-- out       s       
-- out --------------
-- out  not excluded