Unable to parse city name with spaces

Question

I need to extract the city from an address string.

The function below will help me split the address using a space as delimiter.

The code below works if the city name has no spaces, but if the city has a space in the name, it won't work

CREATE TABLE [dbo].[MyCity](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [City] [varchar](255) NULL,
    [State] [varchar](2) NULL,
 CONSTRAINT [PK_MyCity] PRIMARY KEY NONCLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[MyCity] ON
INSERT [dbo].[MyCity] ([ID],  [City], [State]) VALUES (480, N'La Fayette', N'IL')
INSERT [dbo].[MyCity] ([ID],  [City], [State]) VALUES (481, N'La Grange', N'IL')
INSERT [dbo].[MyCity] ([ID],  [City], [State]) VALUES (482, N'La Harpe', N'IL')
INSERT [dbo].[MyCity] ([ID],  [City], [State]) VALUES (483, N'East Saint Louis', N'IL')
INSERT [dbo].[MyCity] ([ID],  [City], [State]) VALUES (484, N'Benton', N'IL')
SET IDENTITY_INSERT [dbo].[MyCity] OFF


CREATE FUNCTION [dbo].[ValueSplit](@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @VALUES TABLE (Param nvarchar(4000))AS
   BEGIN
   DECLARE @chrind INT
   DECLARE @Piece nvarchar(4000)
   SELECT @chrind = 1
   WHILE @chrind > 0
      BEGIN
         SELECT @chrind = CHARINDEX(@Delim,@RepParam)
         IF @chrind > 0
            SELECT @Piece = LEFT(@RepParam,@chrind - 1)
         ELSE
            SELECT @Piece = @RepParam
         INSERT @VALUES(Param) VALUES(@Piece)
         SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
         IF LEN(@RepParam) = 0 BREAK
      END
   RETURN
END
GO


DECLARE @Address AS NVARCHAR(255)
DECLARE @City AS NVARCHAR(255)


--Try the 4 addresses. Benton is a city name with no spaces and works.
SET @Address = '896872 STATE HIGHWAY 14 BENTON'
--'896872 STATE HIGHWAY 14 BENTON'
--'9 RR 10 *BOX 81 LA FAYETTE' --This city name cannot be found
--'642 N 60TH EAST SAINT LOUIS' --This city name cannot be found

--
-- Get City Works for Cities with No Spaces, but fails when the city has a space in the name
--                  
SELECT  @City = City
FROM    dbo.MyCity
WHERE   City IN (
        SELECT  param
        FROM    dbo.ValueSplit(REPLACE(@Address,' ', ','), ',') )

SELECT @City

--
-- A look into the raw split
--                                          
SELECT  param
            FROM    dbo.ValueSplit(REPLACE(@Address, ' ', ','), ',') 

For example when the following address is used: 9 RR 10 *BOX 81 LA FAYETTE

The function will return the city "La Fayette" in position 6 and 7

East Saint Louis is returned in Position 4,5 and 6

How can I match the city name from the provided string to the city table?

Answer 1

If you just need extract City Name from the string, you may SELECT City name as the following

DECLARE @address VARCHAR(100) = '9 RR 10 *BOX 81 LA FAYETTE'

SELECT *
From MyCity
WHERE @Address LIKE '%'+City+'%'