SQL Server search procedure for words in string column from table view

I'm trying to look for all addresses in a table view, that belong to a certain zip code list (500+ zipcodes)

Searching through the different answers, I found something pretty close to what I was looking for in this StackPost

The issue is that I'm dealing with a view (no full-text search) and not a 1 on 1 value.

I tried doing something along the lines of:

set @sql = "SELECT * FROM view开发者_如何学运维TABLE
            WHERE CONTAINS(Address, '"+replace(@list, ',', ' OR ')+"')"

But as mentioned above that doesn't work - I can create a table that will contain the zip code list if that makes things easier, but each column 'Address' will have to be compared to the list of zip code.

Any help is much appreciated!

You can modify your existing code to do something like this with LIKE statement:

set @sql="
    SELECT *
      FROM viewTABLE
     WHERE Address LIKE '%"+replace(@list, ",", "%' OR Address LIKE '%")+"%'"

which will generate @sql like this:

SELECT *
  FROM viewTABLE
 WHERE Address LIKE '%12345%' OR Address LIKE '%22345%' OR Address Like '%55314%'
       -- etc.

However it would be easier if the zipcodes list were in a table:

SELECT *
  FROM viewTABLE v
 WHERE EXISTS
    (SELECT * FROM zipCodeList z
     WHERE v.Address LIKE '%'+z.ZipCode+'%')

This is not the answer but this a start to help you get some ideas of my thoughts to help you solve you problem. I have managed to isolate the numeric values at the beginning of the address column. If you isolate the last characters then you are on your way.

DECLARE @Customer TABLE
    (
      NewPK INT IDENTITY(1, 1) ,
      CustomerAddress NVARCHAR(MAX) NOT NULL
    )
INSERT  INTO @Customer
        ( CustomerAddress
        )
VALUES  ( '10 Fraley St # C, Kane, PA 16735'
        )
INSERT  INTO @Customer
        ( CustomerAddress
        )
VALUES  ( '5611 Market St, Maru, PA 500'
        )
INSERT  INTO @Customer
        ( CustomerAddress
        )
VALUES  ( '5644 N County Road 400 N, Scipio, IN 47273'
        )
INSERT  INTO @Customer
        ( CustomerAddress
        )
VALUES  ( '79 Fraley St # A, Kane, PA 500'
        )
INSERT  INTO @Customer
        ( CustomerAddress
        )
VALUES  ( '5195 N Camelot Way, North Vernon, IN 47265'
        )
INSERT  INTO @Customer
        ( CustomerAddress
        )
VALUES  ( '79 Fraley St # A, Kane, PA 16735'
        )

SELECT  CustomerAddress ,
        CASE WHEN PATINDEX('%[^0-9]%', CustomerAddress) > 0
             THEN SUBSTRING(CustomerAddress,
                            PATINDEX('%[0-9]%', CustomerAddress),
                            CHARINDEX(' ', CustomerAddress + SPACE(1),
                                      PATINDEX('%[0-9]%', CustomerAddress)))
             ELSE CustomerAddress
        END AS 'numeric'
FROM    @Customer