MySQL - match post code based on one or two first characters

Question

I'm trying to create a SQL statement to find the matching record based on the provided post code and stored post codes in the database plus the weight aspect.

The post codes in the database are between 1 or 2 characters i.e. B, BA ...

Now - the value passed to the SQL statement will always have 2 first characters of the client's post code. How can I find the match for it? Say I have a post code B1, which would only match the single B in the database plus the weight aspect, which I'm ok with.

Here's my current SQL statement, which also takes the factor of the free shipping above certain weight:

SELECT `s`.*,
IF (
    '{$weight}' > (
        SELECT MAX(`weight_from`)
        FROM `shipping`
        WHERE UPPER(SUBSTRING(`post_code`, 1, 2)) = 'B1'
    ),
    (
        SELECT `cost`
        FROM `shipping`
        WHERE UPPER(SUBSTRING(`post_code`, 1, 2)) = 'B1'
        ORDER BY `weight_from` DESC
        LIMIT 0, 1
    ),
    `s`.`cost`
) AS `cost`
FROM `shipping` `s`
WHERE UPPER(SUBSTRING(`s`.`post_code`, 1, 2)) = 'B1'
AND 
(
    (
        '{$weight}' > (
            SELECT MAX(`weight_from`)
            FROM `shipping`
            WHERE UPPER(SUBSTRING(`post_code`, 1, 2)) = 'B1'
        )
    )
    OR 
    ('{$weight}' BETWEEN `s`.`weight_from` AND `s`.`weight_to`)
)
LIMIT 0, 1

The above however uses the SUBSTRING() function with hard coded number of characters set to 2 - this is where I need some help really to make it match only number of characters that matches the provided post code - in this case B1.

Marcus - thanks for the help - outstanding example - here's what my code look like for those who also wonder:

First I've run the following statement to get the right post code:

(
    SELECT `post_code`
    FROM `shipping`
    WHERE `post_code` = 'B1'
)
UNION
(
    SELECT `post_code`
    FROM `shipping`
    WHERE `post_code` = SUBSTRING('B1', 1, 1)
)
ORDER BY `post_code` DESC
LIMIT 0, 1

Then, based on the returned value assigned to the 'post_code' index my second statement followed with:

$post_code = $result['post_code'];

SELECT `s`.*,
IF (
    '1000' > (
        SELECT MAX(`weight_from`)
        FROM `shipping`
        WHERE `post_code` = '{$post_code}'  
    ),
    (
        SELECT `cost`
        FROM `shipping`
        WHERE `post_code` = '{$post_code}'
        ORDER BY `weight_from` DESC
        LIMIT 0, 1
    ),
    `s`.`cost`
) AS `cost`
FROM `shipping` `s`
WHERE `s`.`post_code` = '{$post_code}'
AND 
(
    (
        '1000' > (
            SELECT MAX(`weight_from`)
            FROM `shipping`
            WHERE `post_code` = '{$post_code}'
            ORDER BY LENGTH(`post_code`) DESC
        )
    )
    OR 
    ('1000' BETWEEN `s`.`weight_from` AND `s`.`weight_to`)
)
LIMIT 0, 1

Answer 1

The following query will get all results where the post_code in the shipping table matches the beginning of the passed in post_code, then it orders it most explicit to least explicit, returning the most explicit one:

SELECT *
FROM shipping
WHERE post_code = SUBSTRING('B1', 1, LENGTH(post_code))
ORDER BY LENGTH(post_code) DESC
LIMIT 1

Update

While this query is flexible, it's not very fast, since it can't utilize an index. If the shipping table is large, and you'll only pass in up to two characters, it might be faster to make two separate calls.

First, try the most explicit call.

SELECT *
FROM shipping
WHERE post_code = 'B1'

If it doesn't return a result then search on a single character:

SELECT *
FROM shipping
WHERE post_code = SUBSTRING('B1', 1, 1)

Of course, you can combine these with a UNION if you must do it in a single call:

SELECT * FROM
((SELECT *
FROM shipping
WHERE post_code = 'B1')
UNION
(SELECT *
FROM shipping
WHERE post_code = SUBSTRING('B1', 1, 1))) a
ORDER BY post_code DESC
LIMIT 1