hiddn characters kill MySQL qureys


#1

Hey to those who still are here, lol
Just wrote a very very complex script… everything work but one thing…

So basically when i run Qurey in MySQL CC and tryto select from table WHERE type='Life Insurance" i get no returns, now if i go and coppy and paste the type property (Life Insurance) to the sql qurey it works fine…

so after after some crazy times, i went and looked at the character maps of both Life Insurance isnstanses. And i got that that queys are diferent after all

Works: Life°Insurance¶
Doesn't: Life?Insurance¶

As you can see we got 2 diferent space characters… consired the type of my aplication i might assume tha one of them is Windows and another is Linux Based

Here comes the question: How can I make SQL work with both of them no matter what or how can I easily update my application? My app is a module for existing website


#2

Use the LIKE statement and the wild card…

SELECT * FROM table WHERE type LIKE "Life_Insurance"

Note that the _ character is a wild card for a Single charachter while the % character is a wild card for More than one charachter.

Hope this help.


#3

It also might be helpful to “Check” for those annoying charachters and convert them to what you want at the time of Insertion in to the database.

Of course you will need to update your current database to reflect the accurate information. (You could also use the LIKE for an UPDATE statement, and change them offending records.)

UPDATE table SET type = "Life Insurance" WHERE type LIKE "Life_Insurance"

#4

yep LIKE is a good idea, but what i found so far ia that when i use " " to generate the qurey on linux i am getting “degree sign” as space and when im using ’ ’ then im getting “dot” space character so I just changed “” to ‘’
hope that was the cose of the problem because i didnt test it yet

Thanks for your help!