September 27, 2016 at 9:00 am
Hi,
I have the following code:
USE tempdb
GO
GO
/****** Object: Table [dbo].[nhsOrgs] Script Date: 23/09/2016 12:56:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[nhsOrgs](
[id] [int] ,
[Organisation Name] [nvarchar](255) NULL,
[Organisation ID] [nvarchar](255) NULL,
[Office Post Code] [nvarchar](255) NULL,
) ON [PRIMARY]
CREATE TABLE [dbo].[LAOrgs](
[id] [int] ,
[Organisation Name] [nvarchar](255) NULL,
[Organisation ID] [nvarchar](255) NULL,
[Office Post Code] [nvarchar](255) NULL,
) ON [PRIMARY]
GO
INSERT [dbo].[nhsOrgs] ([id], [Organisation Name], [Organisation ID], [Office Post Code]) VALUES (175, N'Royal National', N'RAN', N'HA7 4LP')
INSERT [dbo].[nhsOrgs] ([id], [Organisation Name], [Organisation ID], [Office Post Code]) VALUES (176, N'Brookdale', N'REN', N'BW5 4JZ')
INSERT [dbo].[nhsOrgs] ([id], [Organisation Name], [Organisation ID], [Office Post Code]) VALUES (177, N'London', N'RUF', N'UB2 4RL')
INSERT [dbo].[nhsOrgs] ([id], [Organisation Name], [Organisation ID], [Office Post Code]) VALUES (178, N'Birmingham', N'BHMF', N'B4 8IF')
INSERT [dbo].[nhsOrgs] ([id], [Organisation Name], [Organisation ID], [Office Post Code]) VALUES (179, N'KENT', N'KEN', N'KRT 580')
INSERT [dbo].[LAOrgs] ([id], [Organisation Name], [Organisation ID], [Office Post Code]) VALUES (185, N'Lancashire', N'EYF', N'LW1 5TG')
INSERT [dbo].[LAOrgs] ([id], [Organisation Name], [Organisation ID], [Office Post Code]) VALUES (186, N'Brookdale', N'REN', N'BW5 ERF')
INSERT [dbo].[LAOrgs] ([id], [Organisation Name], [Organisation ID], [Office Post Code]) VALUES (187, N'Swansea', N'SWN', N'SW7 9IL')
INSERT [dbo].[LAOrgs] ([id], [Organisation Name], [Organisation ID], [Office Post Code]) VALUES (188, N'Birmingham', N'BHMF', N'B4 LNR')
INSERT [dbo].[LAOrgs] ([id], [Organisation Name], [Organisation ID], [Office Post Code]) VALUES (189, N'KENT', N'KEN', N'KRT 580')
SELECT *
FROM [nhsOrgs]
-- where [Organisation Name] like '%North Middlesex University Hospital NHS Trust%'
SELECT *
from [dbo].[LAOrgs]
SELECT N.*, L.*
FROM [dbo].[nhsOrgs] AS N INNER JOIN
[dbo].[LAOrgs] AS L ON N.[Office Post Code] = L.[Office Post Code]
DROP TABLE [nhsOrgs]
DROP TABLE [LAOrgs]
At the moment it returns one record in the last SELECT which is correct.
Can someone please tell me how I can amend the query to match the records on the first 2 and 3 letters of the Office post code column. So in the result set I am expecting id 176 = 186 and 178 = 188 to have a match and be listed in my SELECT statement.
Many Thanks
September 27, 2016 at 9:19 am
If you always have a space in the middle of your postcode, and you want to match everything to the left of the space, change your join predicate to this:
LEFT(N.[Office Post Code],CHARINDEX(' ',N.[Office Post Code])) = LEFT(L.[Office Post Code],CHARINDEX(' ',L.[Office Post Code]))
John
September 27, 2016 at 9:38 am
John Mitchell-245523 (9/27/2016)
If you always have a space in the middle of your postcode, and you want to match everything to the left of the space, change your join predicate to this:
LEFT(N.[Office Post Code],CHARINDEX(' ',N.[Office Post Code])) = LEFT(L.[Office Post Code],CHARINDEX(' ',L.[Office Post Code]))John
That is exactly what i needed. Many Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply