Number field that contain slashes

[Bengal313]

I have a mysql database that contain a field that is called id_dumber. It is a 10 digit number seperated by a "-" after the 2nd, 4th, and 7th number. (don't ask why?) . When someone enters the number with the dashes the reults are on the money. But, when someone searches for that field they usually enter just all the number minus the "-". The results are usually not found or there multiple returns. (I am using the "Like" query) Is there a way without going through the huge database and taking out the dashes write a sql statement that will search the database when the dashes are there, and if not will add the dashes in the right places before searching? Thanx

[Jason]

if the value of the number is $num then $num[0] is the first of the entire number. So when you search the db for the number parse it together like "$num[0]"."-".$num[1]"."$num[2]"."$num[3]"."-".....you get the idea...


Jason

[Bengal313]

What if someone enters the numbers and the dashes. Will it work right?

[Jason]

That wont work right, but you can use an if statement to check the length of the number and if they used the dashes then the length would be 3 longer then if they hadn't. You can have two different sql statements to access the DB based on the length.


Jason

[Bengal313]

I know I asked this question a while back. But I didn't get the answer completely. Can some one please explain to me how I can accomplish this. I have a form with a input text box. Now the field in the database that this input textbox is querying against has numbers that are seperated by "-" (example: 13-13-011-010). What I want to do is have people enter either just all numbers like 1313011010 or 13-13-011-010. If they enter with the dashes then it should just query the database. If just numbers are entered it should first add the dashes in the right places and then query the database. Can some one help me out. (php/mysql)

[trib4lmaniac]

if(!strstr($number, '-'))
{
$number=substr($number, 0, 2).'-'.substr($number, 2, 2).'-'.substr($number, 4, 3).'-'.substr($number, 7);
}That should work. (might have got some of the substr start/length digits wrong :cool: ) Will only work if the user enters it in exactly the right way. (with or without hypens, not "with some hyphens")

[raf]

you should store the values without dashes. Especially since you can add the dashes back in when displaying the values.

when a user then enters a number to search for, you do

$number = str_replace('-', '',$number)

or
$bad_chars= array(' ', '-');
$number = str_replace($bad_chars, '',$number);

or
whatever other cleaning function.

To remove the dashes from your current values, you just need to run 1 updatequery once.

UPDATE yourtable SET yourvalue = REPLACE(yourvalue,'-','')