Query not returning correct results

[bsd12673]

I have a query that is supposed to return only records that contain numbers but for some reason doesn't seem to work in Progress. We export our data from Progress to SQL Server and if I run the query in SQL Server it works fine but running in Progress it returns all records. can someone take a look and see if they have any suggestions.

SELECT ShipToNum
FROM Pub.ShipTo
WHERE (CustNum = 10)
AND (ShipToNum NOT LIKE '%[a-z]%')
AND (ShipToNum <> '')

This particular customer has 7 records 1 being blank " ,2,3,4,5,6,hug1" If I run that query in SQL Server it returns "2,3,4,5,6" only. But in Progress it still returns every record.

Thanks,
Ben

 

[regulatre]

The [a-z] clause isn't doing what you expect it to. I just ran a test here and confirmed that Progress (SQL-92?) didn't expand that into "any of a through z" instead I believe it treated it as a literal string.

I also searched the SQL-92 guide for any mention of the bracket chracter and didn't find anything relevant.

I had an idea of adding a constraint that converted the field to an integer, then compared that to its char value, but I couldn't get the to_number function to work - apparently it requires the argument to be a numeric char value and if not, it errors out.

I have a query that is supposed to return only records that contain numbers but for some reason doesn't seem to work in Progress. We export our data from Progress to SQL Server and if I run the query in SQL Server it works fine but running in Progress it returns all records. can someone take a look and see if they have any suggestions.

SELECT ShipToNum
FROM Pub.ShipTo
WHERE (CustNum = 10)
AND (ShipToNum NOT LIKE '%[a-z]%')
AND (ShipToNum <> '')

This particular customer has 7 records 1 being blank " ,2,3,4,5,6,hug1" If I run that query in SQL Server it returns "2,3,4,5,6" only. But in Progress it still returns every record.

Thanks,
Ben