PHP MYSQL query DB and ignore spaces and taxt case

Hi Kalon,

I’m curious; what advantages do you gain by lowering the case of all strings you save in the database?

Personally I wouldn’t do it, however I am interested in how you benefit from doing so.

Cheers,

Anthony.

if there is no technical reason or requirement (from client or whatever) to store the string exactly as entered then I store the string in lower case purely by choice and for the sake of consistancy.

There might not be any advantages, but what I can say is that there have been no disadvantages either for me up till now.

Storing strings in lower case has never prevented me from easily providing any ouputed data from the database according to the client’s specifications.

But as I said before, if the client asked me to store the data in a particular way and I saw problems in then providing their desired output, then I would mention it to them and discuss alternatives and/or better options for storing the data.

so are you saying that you have to ask the client whether it’s okay to destroy the case of surnames like McDonald, or do you make the decision not to destroy it on your own?

only thing i can think of is for a slight speed increase when doing insensitive string comparison (which… mysql automatically does anyway, apparantly… learn something new every day)… even if it was sensitive, my impulse would be to store as-is (data integrity), and use functions for insensitive comparison (SELECT… WHERE LOWER(field) = LOWER(str)), unless i knew for a fact that the table data would be of a specific format.

in the OP’s case…i’m assuming you’re saying the codes are all uppercase, so rajug’s code should work correctly.

Off Topic:

Which is faster, PHP strtoupper() or MYSQL UPPER() ? Or is there a difference? (theoretically they’re both Big-O ‘n’ level functions)

I have a function to reproduce that format so it’s not an issue for me.

So your function takes into account things like Mc, Mac, St, Van der, Von, etc? There’s a lot of possible combinations out there to try and code for. But if thats your choice, go for it.

I didn’t write the function, but it’s served me well up till now :slight_smile:

Thanks for coming back to me Kalon.

Guys, I don’t know if this discussion was because of my wrong solution. And I have to apologize if my solution is directing OP to somewhere wrong way.

But please note what OP has said that he is trying to match a code which I thought for some kind of verifications/confirmation (like sign up confirmation or so) because his example codes seem random codes. For that particular case, I normally do in that way like storing codes all in uppercase and check user entered codes are all in upper case or not in PHP itself. But I don’t check against database for case check. I have never stored other user data like names, surnames, etc. in any lower/upper case and neither I suggest anyone to do that. I just store how they enter.

Hope it is clear why/how I suggested the way.