Trying to get mysql sub string search working

Question

I have a table with postcode districts such as SA1,SA3,SA11, LL41,NP20 and I need to do a search based upon a customer entering a postcode, and then matching the relevant record, i've tried :

SELECT MATCH(postal_district) AGAINST ('SA1 1EG') FROM `postcodes` WHERE MATCH(postal_district) AGAINST ('SA1 1EG') > 0

but it returns nothing, what am I doing wrong?

Answer 1

Not quite sure what you're trying to do, but did you mean this...

SELECT COUNT(*) FROM `postcodes` WHERE MATCH(postal_district) AGAINST ('SA1 1EG');

Answer 2

Try

SELECT * FROM `postcodes` WHERE postal_district LIKE '%SA1%'

I'm not exactly sure what you are trying to do here. If you could try to clarify a little.

Answer 3

The typical syntax for fulltext search in MySQL is:

SELECT * FROM postcodes
WHERE MATCH (postal_district) AGAINST ('SA1 1EG');

Full text search is on only if you add a FULLTEXT index to the column.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

Answer 4

Do you perhaps want to do something more like this?

SELECT postal_district FROM postcodes WHERE  MATCH(postal_district) AGAINST('SA1 1EG' IN BOOLEAN MODE);

I'm not sure a fulltext search is what you really want, either way, as you'd possibly be better served having one code per row and just doing a case-insensitive match (just compare the upper-case'd version of each). If you insist on using the above, make sure you have a fulltext index on the column you're searching against (postal_district).