white spaces in table field.

[incubi]

The data tables I have to work with have white spaces in some fields and if I do a query with a WHERE ='1234" if fails because the queried value looks like this " 1234". So I need to trim out the spaces before the WHERE test. The only option I see is to use StringStripWS but I don't understand how to use that in the query with AutoIT.

Access for example is easy

SELECT co.[co-num], co.[cust-num], co.contact FROM co WHERE (((Ltrim(co.[co-num])) ="1234"));

But that seems to fail using AutoIT and StringStripWS in place of Ltrim

Any help would be great!

Thanks,

Incubi

[martin]

The data tables I have to work with have white spaces in some fields and if I do a query with a WHERE ='1234" if fails because the queried value looks like this " 1234". So I need to trim out the spaces before the WHERE test. The only option I see is to use StringStripWS but I don't understand how to use that in the query with AutoIT.

Access for example is easy

SELECT co.[co-num], co.[cust-num], co.contact FROM co WHERE (((Ltrim(co.[co-num])) ="1234"));

But that seems to fail using AutoIT and StringStripWS in place of Ltrim

Any help would be great!

Thanks,

Incubi

This is just a guess since I know nothing about sql. Maybe you could have

SELECT co.[co-num], co.[cust-num], co.contact FROM co WHERE co.[co-num] IN " 1234";

where there ar more leading spaces in " 1234" than you ever expect.

[Valuater]

I was thinking more like if the value can be captured in a variable, which it appears it can be.

$Data = SELECT co.[co-num], co.[cust-num], co.contact FROM co WHERE (((Ltrim(co.[co-num]))

If StringStripWS($Data) = "1234" Then

; do something

EndIf

.... BUT, I have no clue about sql either

8)

Edited June 11, 2009 by Valuater

[incubi]

I think both ideas will work but in each case the query would have to search through a lot more records until it finds the right one and with a table with 20k + records in it that could take a while. For example in Access if I add the spaces to the number it takes 5x as long to get the hit, whereas if I trim the field before the WHERE test its fast.

Thanks,

incubi