You need to understand two things:
1) any CFML in your SQL string within a <cfquery> tag pair is processed by CF before the SQL string is passed to the DB driver. So your #left()# expression won't be processed "per row", because it's processed before the DB engine does any "per row" operations.
2) QoQ's SQL support is tragically minimalist. This lists all the functions it supports:
http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859 461172e0811cbec0e4fd-7ff0.html#WSc3ff6d0ea77859461172e0811cbec0e4fd-7f cc
With the addition of two string functions: UPPER() and LOWER(). Oh, and CAST() (if that can be seen as a function).
That's it. That page in the docs describes all the functionality QoQ has. There's nothing missing 9as far as I know). That's it. There's not much to it.
I've been lobbying Adobe for a number of years to pull finger and improve QoQ to the point that it's more than just a curio, but they don't seem interested.
As to why your code works on Railo? Railo does some weird things, and doesn't stick to the precedent Adobe dictates their language should follow. Depending on who you talk to, this is either a feature or a barrier to entry (I'm mostly ambivalent, but err towards the latter camp). But perhaps Railo's QoQ processor supports LEFT().
Basically... you're gonna have to approach this differently: loop over the query with <cfloop> (etc) and update it row by row. This is pretty much what QoQ is doing anyhow, so I don't think you'll see a performance degradation. Well: you won't... because you can't do what you want to do with QoQ at all, I guess ;-)
--
Adam
