[SOLVED] Mysql to match only exact digits

[Prodigal Son]

Take for example:

SELECT * FROM table WHERE sid = 0020

 

If it finds sid with 20 it will fetch that data.

 

Is there a way to make mysql match only exact digits? So in order to grab the data with sid of 20, you'd need only "20" and not 020, 0020, 00020, etc.

 

Thanks.

[PFMaBiSmAd]

Leading zeros on numbers have zero significance. To do what you want would require treating the value in the sid column as a string. You would either need to cast the sid column as a CHAR type in the query or you would need to make the sid column one of the string data types.

 

What is the definition of the sid column now and why do you want or need to use leading zeros in an identifier?

[Prodigal Son]

Leading zeros on numbers have zero significance. To do what you want would require treating the value in the sid column as a string. You would either need to cast the sid column as a CHAR type in the query or you would need to make the sid column one of the string data types.

 

What is the definition of the sid column now and why do you want or need to use leading zeros in an identifier?

I actually don't want to use leading zeros. I just only want to match exact digits incase search engines treat it as duplicate content. So a url such as index.php?page=something&sid=020 would also have the same content as index.php?page=something&sid=20. Not sure if search engines will count that as duplicate content. So any leading zeros on the id should go to a 404 page instead of having the same content.

 

Did you mean something like this: SELECT * FROM table WHERE CAST(sid AS CHAR) = 0020

 

I tried that but it still returned the row with id 20.

[PFMaBiSmAd]

Actually, strings would be needed on both sides of the = comparison because of the automatic built in type conversions -

-

SELECT * FROM table WHERE CAST(sid AS CHAR) = '0020'

[Prodigal Son]

Ah oops, of course, forgot to do that. It's working now. Would adding that bit slow down the query much? I noticed that even scripts such as vBulletin wouldn't do that, so I wonder if it's necessary.

[PFMaBiSmAd]

so I wonder if it's necessary.

No. Unless you produce a link on a page with something other than the actual value, a search engine is not even going to know or try anything else. Using the CAST() function will slow down every query with it in it and making the column a character type would only be needed if you intended to have separate values like 20, 020, and 0020 that were for separate rows.