Query for spaces in values

Question

I'm trying to filter a large list to pull out any values that have trailing or leading spaces using a powershell script. I can do it using a where-object command ($items| ? {$_["field"].StartsWith(' ')}), but it's really inefficient and will take a really long time to run. I'm looking to see if there's a way to accomplish this using an spQuery (i.e. CAML). I've tried using CAML with a space in the value but it returns no items.

             <Contains>
                <FieldRef Name='Location' />
                <Value Type='Text'> </Value>
             </Contains>

Using <BeginsWith> comes up with a similarly null result.

Is there a faster way to do this or am I stuck iterating?

Answer 1

I figured out a work around for my issue. Basically, I made a calculated column to find leading or trailing spaces and built my CAML on that. Here's the formula =IF(OR(CODE(LEFT(MYFIELD,1))=32,CODE(RIGHT(MYFIELD,1))=32),"Space","No Space")