2

I'm using Google Sheets with an imported CSV.

The range I perform queries on is called 'Import'

I need to do simple string functions, like REPLACE() and LEN() to evaluate if the data I have is acceptable.

I've been searching, and cannot find any way to manipulate strings in Google Query Language?

Other sources suggest I can use 'Standard' SQL, but I don't seem to be able to?

Any ideas where to look and learn? This language doesn't seem well supported and I couldn't find any IRC groups either.

What I have:

=query(Import,"select A,B,F,E,AL where not AL matches '^[A-Za-z]{2}[ ]{0,1}[0-9]{2}[ ]{0,1}[a-zA-Z]{3}$'")

What I want: (T-SQL) 

=query(Import,"select A,B,F,E,AL where not REPLACE(AL," ","") matches '^[A-Za-z]{2}[ ]{0,1}[0-9]{2}[ ]{0,1}[a-zA-Z]{3}$'")
MLavoie
  • 9,671
  • 41
  • 36
  • 56
FinnLikeDolphin
  • 23
  • 3

2 Answers2

0

Google query language is very limited and there is no function that will give you the requested functionality. You should be able to perform the necessary replacements on your data before you run the query on them, though, using standard Google Sheets functions.

ttarchala
  • 4,277
  • 2
  • 26
  • 36
0

You can manipulate the data while defining the Query Range in the Query Formula itself, e.g.:

=query(ArrayFormula(regexreplace('Filters@0'!F1:L250,"Eliminate ","")),"select Col1, Col7 where Col7 matches '(.*\d{1,3}\.){3}.*'"))
Eduard Besel
  • 1
  • 2