CODEWITHSUNDEEP
google-sheets
Nuno Silva
Nuno Silva

Reputation: 3

GSheets - How to query a partial string

I am currently using this formula to get all the data from everyone whose first name is "Peter", but my problem is that if someone is called "Simon Peter" this data is gonna show up on the formula output.

=QUERY('Data'!1:1000,"select * where B contains 'Peter'")

I know that for the other formulas if I add an * to the String this issue is resolved. But in this situation for the QUERY formula the same logic do not applies.

Do someone knows the correct syntax or a workaround?

Upvotes: 0

Views: 317

Answers (2)

ttarchala
ttarchala

Reputation: 4567

How about classic SQL syntax

=QUERY('Data'!1:1000,"select * where B like 'Peter %'")

The LIKE keyword allows use of wildcard % to represent characters relative to the known parts of the searched string.

Upvotes: 1

Nuno Silva
Nuno Silva

Reputation: 3

See the query reference: developers.google.com/chart/interactive/docs/querylanguage You could split firstname and lastname into separate columns, then only search for firstnames exactly equal to 'Peter'. Though you may want to also check if lowercase/uppercase where lower(B) contains 'peter' or whitespaces are present in unexpected places (e.g., trim()). You could also search only for values that start with Peter by using starts with instead of contains, or a regular expression using matches. – Brian D

It seems that for my case using 'starts with' is a perfect fit. Thank you!

Upvotes: 0

Related Questions