SQL Help

[spivvy]

i am upgrading from interbase 5 to interbase 7 (sql Based) and hit a major snag with a query

on the old server

select * from TABLE where ITEM like '942806%' returns data

on IB7 it returns nothing unless i do

select * from TABLE where ITEM like '%942806%'

but this is not good as it may return multiple sets of data

any one else come across anything similar

i am at a standstill now

[spivvy]

bump

[Don]

Not an Interbase specialist I'm afraid...

Did the syntax change or something - in older versions of squirrel server you used to put an asterisk "*" insetad of percentage sign "%" in LIKE clauses. Worth a two second test.

Is there an index on the ITEM field? Try dropping it and doing the query again then reinstating it.

Did your underlying data change during the upgrade?

Hope any of the above sparks off an idea for you...

[Graham]

Strange, as has been said i'd double check the syntax for like and the pattern matching just to see if anything has changed.

I also find it usefull sometimes to create a new table, populate it with a small amount of data and play with that, when i have a similar problem. That rules out a data issue and is usually faster as of course there is less data to run through

G

[Wiggy001]

Check the data first to ensure that string does exist. If it appears to, double check to make sure there is not an embedded NULL at the beginning of the field. Don't ask me how as I've never found out, but I had that on a sql-server db a while back (how can a char field, defined as NOT NULL, contain a NULL followed by data?)

Just a thought

[ATG]

I'd do as Wiggy001 suggests and take a careful look at the values in the Item field. It would be rather odd if such a basic thing as LIKE's behaviour had been changed. More likely the data got a bit fried during the upgrade.

[tigger1]

Find out a line should return data with your original : -

LIKE '876596%'

query. Then check it doesn't actually have leading zero's in it in the updated table. Sounds possible. Would happen if the newly created table was specified with data length that was longer than the original one.