Splitting value in database in WHERE clause-CodePudding

I am querying a BigQuery table to extract building data. We are storing building data in a cell, together with location and sensor name data. The building row has the following values e.g.

GB-FRE-BB2003_MSU-01
GB-FRE-BB2001_MSU-12
GB-FRE-BB2003_MSU-12
GB-FRE-BB2012_MSU-12
GB-FRE-BB2003_MSU-10

etc

I would like to query the data, using a substring, so I can find all the data from the BD2003 building, regardless of location and sensor.

SELECT `presentvalue` 
FROM `database` 
WHERE ???

Is someone able to help with this? I have looked at SPLIT and SUBSTRING but can't seem to get the query right.

CodePudding user response：

use where substring(split(val , '-')[offset(2)], 1, 6)='BB2003'

tested it on the below code and it works:

create temp table sample(
val string
);
insert into sample
(val)
values 
('GB-FRE-BB2003_MSU-01'),
('GB-FRE-BB2001_MSU-12'),
('GB-FRE-BB2003_MSU-12'),
('GB-FRE-BB2012_MSU-12'),
('GB-FRE-BB2003_MSU-10');

select *,  from sample where  substring(split(val , '-')[offset(2)], 1, 6)='BB2003''

CodePudding user response：

Few more options

Using LIKE

select *
from your_table
where presentvalue like '%-BB2003_%'

Using REGEXP_CONTAINS

select *
from your_table
where regexp_contains(presentvalue, '-BB2003_')

if applied to sample data in your question - both have below output