CODEWITHSUNDEEP
google-bigquery
john
john

Reputation: 13

Big query filter out numbers

how to query data in big query that only outputs data with number in them , like filter out words vs words that has numbers , example data is :

my attempt

SELECT
count (*)
FROM
  `lucky-antler-315014.th.st words`
WHERE
string_field_0 LIKE '%1%'or
string_field_0 LIKE '%2%'

the results

the total count for these two is 170, but its independent count for LIKE '%1%' is 145 and for %2% is 79 , yet the total count for the above attempt is only 170 when its should be total count of 224 when added right?

Upvotes: 1

Views: 2661

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

As it was already suggested in comments - you should use regexp as in below example

select *
from `project.dataset.table`
where regexp_contains(string_field_0, r'\d')

if applied to sample data in your question - output is

enter image description here

Upvotes: 1

john
john

Reputation: 13

i believe i got it now , in order for me to extract my desired --data containing numbers , i got the solution for it:

WHERE 
  string_field_0 LIKE '%1%'
  OR string_field_0 LIKE '%2%'
  OR string_field_0 LIKE '%3%'
  OR string_field_0 LIKE '%4%'
  OR string_field_0 LIKE '%5%'
  OR string_field_0 LIKE '%6%'
  OR string_field_0 LIKE '%7%'
  OR string_field_0 LIKE '%8%'
  OR string_field_0 LIKE '%9%'
  OR string_field_0 LIKE '%0%'

Upvotes: 0

Sourav Halder
Sourav Halder

Reputation: 56

So here you are not considering the case if the value has both '1' and '2' in it. so in this case if the value is '12-something' that will be counted both times you query the numbers individually. But in case you put a 'or' in query and check for any numbers then this value will be considered only once.

However, for this query I would suggest using regular expressions which might have been easier than the long query.

Upvotes: 0

Related Questions