CODEWITHSUNDEEP
sqlgoogle-bigquery
MustardRecord
MustardRecord

Reputation: 305

BigQuery: select rows where column value contains string

I would like to know how to know how to filter a table by a specific column, when this column contains a specific subtring.

Here's an example of my table:

enter image description here

I would like to obtain those rows where the column tsBegin contains 2020-08-04, maybe with something like:

SELECT * FROM mytable
where '2020-08-04' in tsBegin

Upvotes: 0

Views: 2146

Answers (2)

GMB
GMB

Reputation: 222432

Use date functions, and half-open intervals:

select * 
from mytable
where tsbegin >= date '2020-08-04'
  and tsbegin < date_add(date '2020-08-04', interval 1 day)

Although a bit lenghtier to type, direct filtering against literal values is usually much faster than applying a date function on the column being filtered, as in where date(tsbegin) = date '2020-08-04'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Date operations:

where date(tsBegin) = date '2020-08-04'

A column named tsBegin should not be a string column, so you just want the date.

If tsBegin is a string, I would suggest that you convert it to a timestamp.

Upvotes: 1

Related Questions