We help IT Professionals succeed at work.

SQL - Find Records Where Values Match Characters in Comma Delimited String

572 Views
Last Modified: 2012-05-06
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"

Open in new window

Comment
Watch Question

Commented:
Create an UDF that parses delimited strings to a table like the one in the link below -

https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22819980.html?sfQueryTermInfo=1+10+dbo.fnsplit

Then just do an inner join on this UDF like the template SQL code below (code from line 7 to 12 in your code being replaced with the code below)
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
inner join dbo.fnsplit(' + REPLACE(rsC9__varS4, "'", "''") + ', ',') F ON C.CommCode1 LIKE ''%'' + F.SplitCol + ''%''
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
ORDER BY C.CommunityName ASC"

Open in new window

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for your help with this.  Your solution worked great...

Author

Commented:
Excellent.  Thank you.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.