mysql search without spaces

Question

I have a list of names in my database and I have to find the id of the name that I pass in the url.

My problem is that the names I pass in the url will not have a space in them while the saved record will in the database. When I search the database I get no records found.

e.g database record is "My Name" while what I will be passing in the url and searching with is "myname"

if(isset($_GET["name"])) $name = $_GET["name"];

SELECT id
FROM table
WHERE name Like '%$name%'

Thanks for any help.

How are you setting your value in the URL, you could give a delimiter and then explode it using that delimiter, effectivly separating your name. Or something like that — Bono
– Bono, Commented May 18, 2012 at 14:17
So you call your script like script.php?name=myname or script.php?name=my%20name? ... and why you even use GET and not POST? — djot
– djot, Commented May 18, 2012 at 14:17
You have a mass security hole here. Read about SQL injection. — Madara's Ghost
– Madara's Ghost, Commented May 18, 2012 at 14:18
yes this is just for testing, I just used get in this example, I use script.php?name=myname — Keith Power
– Keith Power, Commented May 18, 2012 at 14:19

djot · Accepted Answer · 2012-05-18 14:42:22Z

6

// id don't know the exact syntax, but this is what you are searching for I guess:

// replace spaces with nothin temporarily, then check equal (=) not like (%%) if name is exactly the same (except the spaces)

SELECT id, REPLACE(name, ' ', '') AS tmp FROM table WHERE tmp='%$name%'

answered May 18, 2012 at 14:42

djot

2,9474 gold badges22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GDP · Accepted Answer · 2012-05-18 14:22:42Z

4

Obviously not the right way to store or search for, but if you must, try replacing the spaces with blanks like this:

SELECT id
FROM table
WHERE REPLACE(`name`,' ','') LIKE '%$name%'

answered May 18, 2012 at 14:22

GDP

8,1636 gold badges48 silver badges84 bronze badges

Comments

King Skippus · Accepted Answer · 2012-05-18 14:18:44Z

2

I think best practice is to store another column, called something like name_nospaces and insert myname as a calculated value into it when you insert the My Name record. You could also create a view that contains myname as a column and query for it, but the advantage of another column in the original table is that it can be indexed for fast retrieval.

answered May 18, 2012 at 14:18

King Skippus

3,8281 gold badge26 silver badges24 bronze badges

2 Comments

King Skippus Over a year ago

Also, I don't think that MySQL supports calculated columns directly. There are clever workarounds such as creating a trigger to calculate the value on insert. If you're not using triggers for anything else, though, to ensure that your database isn't overly complicated, I personally think it's easier just to insert the calculated values with the rest of the data. (INSERT INTO blah VALUES ('My Name', 'myname');) If you want to use triggers, reference this: stackoverflow.com/questions/660417/…

Keith Power Over a year ago

thanks, seems the beast way, I was hoping that there might be something in mysql to tell it ignore spaces when searching, but that is not the case

Collectives™ on Stack Overflow

mysql search without spaces

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related