CODEWITHSUNDEEP
sqlgoogle-bigquery
clucko87
clucko87

Reputation: 89

A string does not contain a number

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.

Upvotes: 0

Views: 1550

Answers (1)

Cylldby
Cylldby

Reputation: 1978

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

enter image description here

Upvotes: 2

Related Questions