Match Postcodes ad return part that matches

[ashley25]

I have been given a list of UK postcodes, with the following format L15TG or TS14TGU.

I need to be able to match these postcodes to a list of postcodes I have stored in the database, however, my list are only UK outcodes, so L15TG is just L1 and TS14TGU is just TS14.

So I need to match the records and return the part of the string that matches i.e take L1 from L15TG.
Can someone assist?

 

[vbaInet]

Basically you have two tables and you want to match them?

 

[ashley25]

In a way yes. But the fields dont match in current format. So I wanted to convert my postcode list from L15TG to L1 so it will match my other table.

 

[vbaInet]

Don't worry I understood that part.

You can do it in one of two ways:

1. Normally you do an INNER, LEFT or RIGHT JOIN, but you can also do a LIKE JOIN. That way you can match L15TG LIKE L1*.
2. Use the WHERE part to match it using a LIKE operator or Instr().

Try both and see which one is quicker.

By the way, does the field containing the shorter version have data in all records?

 

[ashley25]

i understand the like operator however i cant get it to work in the situation. i am not searching for a particular set of records just where one field resembles another

 

[vbaInet]

Again, I understand the objective and I've mentioned two ways you can accomplish that.

WHERE Table1.[Field] LIKE Table2.[Field] & "*"

Translate that to a LIKE JOIN as well.