CODEWITHSUNDEEP
phpmysql
matteodallombra
matteodallombra

Reputation: 63

Match full string with partial value in MySQL column (postcodes)

I think the solution to this problem should be easy, but I can't find a good way around it.

I'm working with UK postcodes. My reference db looks like this:

+----+----------+----------+
| ID | postcode | leadtime |
+----+----------+----------+
|  1 | AB10     |        1 |
|  2 | AB11     |        2 |
|  3 | B7       |        3 |
|  4 | SE16WD   |        1 |
+----+----------+----------+

The value in this table only represents the first half of a full UK postcode, but that's all we need to check against.

Now, the user has a form where they can type their FULL postcode to check what the lead time will be (the full postcode is then use as part of the delivery address).

Uk postcodes can have different length and you can type them with or without spaces between the first and the second half. Right now I have some PHP code that would basically take whatever was typed by the user and try to run it through all the different layout possibility, but it doesn't always work.

I'd like to find a way to do so in MySQL. I've started playing around with

SELECT leadtime FROM myTable WHERE postcode LIKE '$postcode%'

But this only works for the first few digits. As soon as the user types the entire postcode, this query won't find anything in the table.

I cannot for the life of me, find a way to match the two values!!

I'm sure I'm missing something really basic here!

Thanks a lot for your time and help with this.

EDITED the MySQL table to better reflect the variety in reference postcodes to check against.

Upvotes: 0

Views: 1408

Answers (4)

JYoThI
JYoThI

Reputation: 12085

try like this

replace space by wildcard 

select USERNAME FROM LOGIN_USERS WHERE USERNAME LIKE CONCAT('%',REPLACE('user_inPut',' ','%'),'%');

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

SELECT * FROM user;
+---------+--------+
| user_id | user   |
+---------+--------+
|       3 | George |
|       1 | John   |
|       2 | Paul   |
|       4 | Ringo  |
+---------+--------+

SELECT * FROM user WHERE 'Ringo Starr' LIKE CONCAT(user,'%');
+---------+-------+
| user_id | user  |
+---------+-------+
|       4 | Ringo |
+---------+-------+

Upvotes: 2

user4244405
user4244405

Reputation:

You can always:

  • make a "broader" selection by using substr($postcode, 0,2);
  • then loop through the results and trim off the alphabetic characters from the beginning of each result using regex; then parse the numeric string to number using intval();
  • and finally make a number-range comparison to narrow down the result.

The above technique may be a bit more complex, but you will have more control over the final results.

If you need a code example of how to achieve this, kindly comment and I will update the answer.

Upvotes: 0

arzhed
arzhed

Reputation: 438

I see no other way around than stripping the postcode from whitespaces and comparing your DB 'postcode' field with a 4 characters substring of that postcode.

Upvotes: 0

Related Questions