SQL: LIKE where condition specifying a letter followed by a number followed by anything else?

Question

Hi, Is it possible to write a WHERE condition in MS SQL Server that will grab rows that:

• begin with a specified letter
• then have a decimal
• then anything else

I basically have a table that contains postcodes and I want all postcodes that belong to Birmingham - B followed by a number. Some postcodes are in there that start B and then another letter which do not belong to Birmingham, so I can't simply useLIKE 'B%'.

Thanks!

Answer 1

Use LIKE 'B[0-9]%'. The "[0-9]" indicates any character in the range '0'-'9'.

Also bear in mind that (a) someone will have typed in an "O" instead of a "0" in some cases, and that there may be some international postcodes that also start with B and a number, so if you've got a reliable country field, you should check that, too :) Yes, I work with postcodes a lot :)

Answer 2

I think you can do something like this:

Select * From PostCodes
Where PostCode Like 'B[0-9]%'

Answer 3

SELECT postc FROM postcodes WHERE postc LIKE 'B[0-9]*%';

Something along these lines!

You could also do:

SELECT
    postc as ps,UPPER(SUBSTR(ps),1,1) as firstLetter,SUBSTR(ps,2,1) as secondNumber
FROM
    postcodes
WHERE
    firstLetter = 'B' AND secondNumber LIKE '[0-9]%'

Something along them lines.

Answer 4

MySQL has regular expressions (REGEXP/RLIKE) built in. I think that you could add the functionality to others, such as SQL Server.

This would give you more precision, e.g.

SELECT * FROM PostCodes WHERE PostCode RLIKE '^B[[:digit:]]{1,2}[:space:]'

This would also allow better matching of possible typos in the condition.

Answer 5

According to the Wikipedia article on the B postcode area:

• B39 is invalid
• B95 is Henley-in-Arden,
  Stratford-on-Avon -- would you
  consider Henley-in-Arden to "belong
  to Birmingham"? (maybe it is, I really don't know)

Perhaps safest to expand out the codes you consider to be Birmingham:

postcode LIKE 'B1 %'
OR postcode LIKE 'B2 %'
OR postcode LIKE 'B3 %'
...
OR postcode LIKE 'B10 %'
OR postcode LIKE 'B11 %'
OR postcode LIKE 'B12 %'
...

Those prefixes could of course be stored in a table.