I am using a PATINDEX in a query to find values within a comma delimited string. However, if a string is holding values (Z, A), the query only returns those records where both Z and A are contained in the string and in that order. What I need is a SQL function that would find records that contain both the Z and the A (Z , A) or A and Z (A , Z) or only Z or only A. The wild cards % does not seems to help find records where the code is a single letter and does not meet both criteria.
DECLARE @long1 decimal(5,2);
DECLARE @lat1 decimal(5,2);
DECLARE @rangeFactor decimal(7,6);
SET @rangeFactor = 0.014457;
SELECT @lat1 = LATITUDE, @long1 = LONGITUDE
FROM dbo.zipcodes where zipcode = '" + Replace(rsC9__varZ2, "'", "''") + "';
SELECT DISTINCT(C.CommunityName), B.ABBR, B.Latitude, B.Longitude, B.zipcode, C.CommunityID, C.CommCity, C.CommState, C.CommZip, CommCode1
FROM dbo.zipcodes B inner join dbo.Communities C ON B.ZipCode = C.CommZip
WHERE B.LATITUDE BETWEEN @lat1-(100 *@rangeFactor) and @lat1+(100 *@rangeFactor)
AND B.LONGITUDE BETWEEN @long1-(100 *@rangeFactor) and @long1+(100 *@rangeFactor)
AND dbo.getDistance(@lat1,@long1,B.latitude,B.longitude) <= 100
AND (PATINDEX('%" + Replace(rsC9__varS4, "'", "''") + "%', CommCode1) > 0)
ORDER BY C.CommunityName ASC"