Get single result from table that are LIKE ...

[jason310771]

I would like to know how I can get a single result from a table that is like a phrase.

 

My table has all the postcodes like so...

 

B1

B2

B3

CV2

WC1A

 

and if someone enters say B1 2AB or WC1A 1AB that I wish to find the single result that is like the one entered and obtain the other details in the results.

 

What query could I use to do this.

 

 

My table looks like this...

 

 

 

CREATE TABLE IF NOT EXISTS `postcodes` (
  `Pcode` text NOT NULL,
  `Grid_N` int(11) NOT NULL DEFAULT '0' COMMENT 'used',
  `Grid_E` int(11) NOT NULL DEFAULT '0' COMMENT 'used',
  `Latitude` decimal(12,5) NOT NULL DEFAULT '0.00000',
  `Longitude` decimal(12,5) NOT NULL DEFAULT '0.00000',
  `town` varchar(255) DEFAULT NULL COMMENT 'THIS FIELD WAS ADDED TO USING THE REGION INFO AS SOME TOWNS WERE BLANK',
  `region` varchar(255) DEFAULT NULL COMMENT 'this is now not used',
  `country` varchar(3) NOT NULL COMMENT 'this is now not used',
  `country_string` varchar(16) NOT NULL COMMENT 'used',
  `state` text NOT NULL,
  `mydummy` text NOT NULL,
  KEY `x` (`Grid_N`,`Grid_E`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='DanGibbs.co.uk Free UK Postcode Database';

INSERT INTO `postcodes` (`Pcode`, `Grid_N`, `Grid_E`, `Latitude`, `Longitude`, `town`, `region`, `country`, `country_string`, `state`, `mydummy`) VALUES
('AB10', 392900, 804900, '57.13514', '-2.11731', 'Aberdeen', 'Aberdeen City', 'SCT', 'Scotland', '', 'UNITED KINGDOM'),
('AB11', 394500, 805300, '57.13875', '-2.09089', 'Aberdeen', 'Aberdeen City', 'SCT', 'Scotland', '', 'UNITED KINGDOM'),
('AB12', 393300, 801100, '57.10100', '-2.11060', 'Aberdeen', 'Aberdeen City', 'SCT', 'Scotland', '', 'UNITED KINGDOM'),
('AB13', 385600, 801900, '57.10801', '-2.23776', 'Milltimber', 'Aberdeen City', 'SCT', 'Scotland', '', 'UNITED KINGDOM'),
('AB14', 383600, 801100, '57.10076', '-2.27073', 'Peterculter', 'Aberdeen City', 'SCT', 'Scotland', '', 'UNITED KINGDOM'),
('AB15', 390000, 805300, '57.13868', '-2.16525', 'Aberdeen', 'Aberdeen City', 'SCT', 'Scotland', '', 'UNITED KINGDOM'),
('AB16', 390600, 807800, '57.16115', '-2.15543', 'Aberdeen', 'Aberdeen City', 'SCT', 'Scotland', '', 'UNITED KINGDOM'),
('AB21', 387900, 813200, '57.20960', '-2.20033', 'Aberdeen Airport', 'Aberdeen City', 'SCT', 'Scotland', '', 'UNITED KINGDOM'),
('AB22', 392800, 810700, '57.18724', '-2.11913', 'Bridge Of Don', 'Aberdeen City', 'SCT', 'Scotland', '', 'UNITED KINGDOM'),
('AB23', 394700, 813500, '57.21242', '-2.08776', 'Aberdeen', 'Aberdeen City', 'SCT', 'Scotland', '', 'UNITED KINGDOM');
 

[Barand]

You could do this (where you enter AB21 1AB, say)

SELECT Pcode
, Latitude
, Longitude
FROM postcodes
WHERE 'AB21 1AB' LIKE CONCAT(Pcode,'%') 
;

BUT there is a more efficient way.

 

Put an index on Pcode (primary key?) and set its type to VARCHAR. A TEXT type can hold a novel and is overkill for 4 characters.

 

Then, as the final part of a postcode is always 3 characters, remove those and trim off any space and search for the remaining Pcode = 'AB21'

[jason310771]

Thank you for this answer.  I was thinking of this and opted to have them enter the full formatted postcode and not one without the space or part code.  if they entered B12 then this would get results for B12 but if their postcode was in fact B1 2AB then they would not get the correct results as the my scripts would think they meant B12  !  I was trying to cover too many angles...  Seems to work so far.

Thanks for your input and best method.