Looking for addresses in a table that does not contain a number

So "Smith Street" or "James Road" etc

I tried using:

address_street not like '%[0-9]%'

but this did not work, as the results returned everything that is not literally that string.

CodePudding user response：

For this kind of investigation with strings you can use regular expressions and the corresponding functions offered by BigQuery. For instance with REGEX_CONTAINS:

with sample as (
    select "39, Albert street" AS address_street
    union all select "Private Drive"
    union all select "10, Downing Street"
    union all select "Buckingham palace"
)

select * from sample 
where not regexp_contains(address_street, r'\d')

returns