Question: I have a database of dealerships who are allocated the outward element of UK postcodes as their "patch". That is, typically, 1 or 2 characters, so for example a dealership in Andover will look after any enquiry from a postcode beginning DT. However some dealerships share a patch, for example, one dealership might look after GU25 where another looks after GU26.

I need to be able to identify the correct dealership using a query.

So far I've managed to extract the outward element of the postcode from an enquiry form submission. 

Now I need to search through the database. However an exact match is not going to work as I need to retain the full outward element in case a patch is split. 

**Examples.** 

1) A customer enters DT1 1XL. I extract "DT1" but I need to match this with "DT".

2) A customer enters GU25 4QF. I extract "GU25" and I need to match this with GU25.

```php
$sql = "SELECT * FROM table WHERE outward='DT1'"; 

```

I'd like this to essentially return, "*there's no exact match but we did find* **DT**".

Or if we searched for GU25, "*there's an exact match!*".

So I need to find the closest match essentially. Any ideas would be hugely appreciated.

Answer: 
There will be many, many ways to skin this cat. No matter what you settle on, you should employ the best practice of making minimal trips to the database. I mean, a single trip that makes a UNION of two queries is better than two separate trips.

Here is one query that you could use: ([Demo](https://www.db-fiddle.com/f/mWHgBHDTCJ7uyseovygtRe/0))

**Schema (MySQL v5.7)**

```
CREATE TABLE `test` (
    `outward` varchar(255)
);

INSERT INTO `test` VALUES
('AB5 8YU'),
('DT1 1XL'),
('DT2 9XL'),
('DT25 1XL'),
('DT11 4XL'),
('GU25 4QF'),
('GU2 3RN');

```

---

**Query**

```
SELECT outward,
FROM test
WHERE outward LIKE 'DT%'
ORDER BY
    IF(LOCATE('DT1 ', outward), 0, 1),
    CAST(SUBSTRING_INDEX(REPLACE(outward, 'DT', ''), ' ', 1) AS UNSIGNED);

```

Result set:

```
| outward  |
| -------- |
| DT1 1XL  |
| DT2 9XL  |
| DT11 4XL |
| DT25 1XL |

```

The result set always orders the exact matches before the close matches. As you iterate the result set rows, you can perform a quick check for an exact match with `strpos()`. Or you can pass a "matchType" flag in the result set to make processing even easier. 
(<https://www.db-fiddle.com/f/mWHgBHDTCJ7uyseovygtRe/1>)

Be sure to validate/sanitise your user input before feeding those variables into your query. And use prepared statements with placeholders.

You will need two separate substrings for your query:

1. The leading letters
2. The characters up to and including the first encountered space. (Notice the space included in the `LOCATE()` call.)

In the future, when you have mysql question, please provide a dbfiddle link in your question. This clarifies the schema, gives volunteers an instant and accurate playground to test on, and this will inevitably yield better, faster, and more correct answers for you.
In the end I solved it with a two stage query process.

The first stage was to look for a match of the complete outward element, if that failed to yield results I used

```
$outward = preg_replace('/[0-9]+/', '', $outward);

```

to remove the number element of the outward and ran the search again. 

It'd be good if the tech existed to find the closest match but this solution wasn't too bulky and works.