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
精彩评论