CODEWITHSUNDEEP
sqlgoogle-bigquery
Daniel Lee
Daniel Lee

Reputation: 8011

Bigquery SELECT * FROM table where column = 'string' not returning any values

When I run the following query in bigqeury I am getting no results.

SELECT COUNT(*) FROM raw.bicc_customers WHERE sub_type = "SUD"

I don't get any results

However, when I run

SELECT COUNT(*) FROM raw.bicc_customers WHERE sub_type LIKE "%SUD%"

I get results. The field that I'm looking for is SUD with no spaces, I don't understand why I need wildcards to find it. What am I missing?

EDIT:

So in the table the column is actually "SUD" - How would you find this without wildcards?

Upvotes: 0

Views: 3806

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

So in the table the column is actually "SUD" - How would you find this without wildcards?

Option 1 - escaping double quotes - see Escaping special characters in strings for more details

SELECT COUNT(*) 
FROM raw.bicc_customers 
WHERE sub_type = "\"SUD\""

Option 2 - using single quotes

SELECT COUNT(*) 
FROM raw.bicc_customers 
WHERE sub_type = '"SUD"'

Upvotes: 1

user6589747
user6589747

Reputation:

I couldn't add a comment, because I'm too new.

But try doing this query to help you determine what's wrong.

SELECT LENGTH(sub_type ) FROM raw.bicc_customers WHERE sub_type LIKE "%SUD%"

If you see that all the length values are greater than 3, that means you have some white space characters around it.

You can also do this to check: 

SELECT LENGTH(sub_type ) FROM raw.bicc_customers WHERE LTRIM(RTRIM(sub_type)) = "SUD"

Upvotes: 3

Related Questions