471,073 Members | 1,398 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,073 software developers and data experts.

Brick Codes

Hi,

I have a table of Locations around the country. My system produces reports
based on these Locations. I also have a table containing Brick Codes e.g.

Brick Post Code
AB51 AB51
AB52 AB52
AB55 AB55
AB56 AB56
AL01 AL1
AL02 AL2
AL03 AL3
AL04 AL4
How can I compare the first 3 or 4 letters of the postcode in the Locations
table to the corresponding entry in the Brick Codes table so I can add it to
my report?

Thanks for your help
Jul 20 '05 #1
2 3559
"Shaun" <no****@nospam.com> wrote in message
news:YL*******************@stones.force9.net...
Hi,

I have a table of Locations around the country. My system produces reports
based on these Locations. I also have a table containing Brick Codes e.g.

Brick Post Code
AB51 AB51
AB52 AB52
AB55 AB55
AB56 AB56
AL01 AL1
AL02 AL2
AL03 AL3
AL04 AL4
How can I compare the first 3 or 4 letters of the postcode in the Locations table to the corresponding entry in the Brick Codes table so I can add it to my report?

Use the substring function to get the n letters and the case
function to compare, such as ...

case when substring(brick,1,2) = substring([post code],1,2) then 'matching
1st 2' else 'no match' end "Comparison 1 of x",
case when substring(brick,1,3) = substring([post code],1,3) then 'matching
1st 3' else 'no match' end "Comparison 2 of x",
case when substring(brick,1,4) = substring([post code],1,4) then 'matching
1st 4' else 'no match' end "Comparison 3 of x",
etc

Jul 20 '05 #2
Shaun,

This is untested, but should do the trick.

WHERE Brick = CASE LEN(PostCode)
WHEN 3 THEN STUFF(PostCode,3,0,'0')
ELSE PostCode END

or

WHERE PostCode = CASE SUBSTRING(Brick,3,1)
WHEN '0' THEN STUFF(Brick,3,1,'')
ELSE Brick END
Hope that helps,
Rich
"Shaun" <no****@nospam.com> wrote in message
news:YL*******************@stones.force9.net...
Hi,

I have a table of Locations around the country. My system produces reports
based on these Locations. I also have a table containing Brick Codes e.g.

Brick Post Code
AB51 AB51
AB52 AB52
AB55 AB55
AB56 AB56
AL01 AL1
AL02 AL2
AL03 AL3
AL04 AL4
How can I compare the first 3 or 4 letters of the postcode in the Locations table to the corresponding entry in the Brick Codes table so I can add it to my report?

Thanks for your help

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Shane Groff | last post: by
6 posts views Thread by Josh Mcfarlane | last post: by
2 posts views Thread by Shaun | last post: by
18 posts views Thread by Steve Litvack | last post: by
3 posts views Thread by c# beginner | last post: by
5 posts views Thread by =?GB2312?B?17/HvyBaaHVvLCBRaWFuZw==?= | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.