Generating Real UK Postcodes

Looking for help generating realistic looking postcodes for Manchester

I’ve used the following expression however it seems only to be using 10-18 and ignoring all other values. In addition, it seems to be generating mostly M16 M17 M18

How would I set this up so that I get an equal(ish) distribution of all postcodes?

M((1|2|3|4|5|6|7|8|9|10|11|12|13|14|15|16|17|18){1}) \d{1}[:upper:]{2}

I originally tried using a similar expression for a wider range of postcodes however after downloading 9000 rows in a file; not a single one contained an M postcode.

(M|B|OX|EX|SN|WS|AB|EC|GU|MK|ST|BT){1})((1|2|3|4|5|6|7|8|9|10|11|12|13|14|15|16|17|18){1}) \d{1}[:upper:]{2}

Hi
Here’s a possible solution for you (it’s maybe “overkill”, but quite flexible - if you ever need any other postcodes), using some database programming.
I have used M1-M18, then 1 digit (0-9), then 2 letters.
I’m not quite sure which “double” letter combinations are valid (at the end of the postcode), so my query generates all 676 possibilities (AA-ZZ).
I got 121680 combinations by doing this. You can get an evenly distributed sample by using the last query.

I know that it would be a tall order to understand and run the code if you are new to databases - but maybe this type of coding is not a problem for you :slight_smile: Cheers
Stefan

-- ----------------------------------------------------------------------------
-- create and populate 3 small tables
-- ----------------------------------------------------------------------------
-- pc1: hold the first (couple of) letter(s)
create table pc1 (
  prefix varchar2(5) unique
);

-- pc2: hold all possible digits
create table pc2 (
  digit varchar2(1) unique
);

-- pc3: hold all possible letters
create table pc3 (
  postfix varchar2(1) unique
);

-- populate the tables
begin
  for pre in 1 .. 18
  loop
    insert into pc1 values ( 'M' || pre );
  end loop; 
  for digit in 0 .. 9
  loop
    insert into pc2 values (digit);
  end loop;
  for i in 1 .. 26
  loop
    insert into pc3 values ( chr(i + 64) );
  end loop;
end;

-- ----------------------------------------------------------------------------
-- SELECT all possible postcodes
-- ----------------------------------------------------------------------------

select prefix || ' ' || digit || letters postcode
from pc1, 
     pc2, 
     ( select L1.postfix || L2.postfix letters
       from pc3 L1, pc3 L2 )


-- postcode count
select count(*)
from pc1, 
     pc2, 
     ( select L1.postfix || L2.postfix letters
       from pc3 L1, pc3 L2 )

--> 121680

-- ----------------------------------------------------------------------------
--   use a sequence for numbering the postcodes, and store them in a table
-- ----------------------------------------------------------------------------

create sequence pc_seq
start with 1
increment by 1

create table postcodes
as 
select pc_seq.NEXTVAL id, prefix || ' ' || digit || letters postcode
from pc1, 
     pc2, 
     ( select L1.postfix || L2.postfix letters
       from pc3 L1, pc3 L2 )

-- ----------------------------------------------------------------------------
-- select how ever many postcodes you need 
-- (change the number in the mod() function call)
-- ----------------------------------------------------------------------------
select * from postcodes
where mod(id, 1000) = 0