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

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

LVL 1
CraigDeringtonAsked:
Who is Participating?
 
8080_DiverConnect With a Mentor Commented:
The provided code looks somewhat like a stored procedure or a portion thereof.  What you need to do is to break the comma separated list of characters into something that will work (perhaps an in-memory table) and then build your SQL query so that it makes use of that something.  
I am assuming that rsC9__varS4 is an input variable to the stored procedure.  So, the following code should be something like what you will need.
By the way, DISTINCT doesn't work on a column by column basis but on a row by row basis, so you DISTINCT(C.CommunityName) isn't really correct syntax. ;-)
 

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, "'", "''") + "';
 
DECLARE @TempListTable TABLE
                       (
                        ACharacter AS VarChar(10)
                       );
 
DECLARE @TempChars AS VarChar(10);
DECLARE @WorkString AS VarChar(8000);
DECLARE @CommaPos AS INT;
 
-- Set the WorkString variable to an empty string
SET @WorkString = '';
 
-- Find the position of the first comma, if any
SET @CommaPos	=	CharIndex(',', rsC9__varS4);
 
IF (@CommaPos > 0)
THEN
BEGIN
-- We got a comma, so add the first string and set the WorkString to the rest
	INSERT LEFT(rsC9__varS4, @CommaPos - 1) INTO @TempListTable ;
	SET @WorkString = RIGHT(rsC9__varS4, LENGTH(rsC9__varS4) - @CommaPos);
END
 
WHILE (@WorkString <> '')
BEGIN
	SET @CommaPos	=	CharIndex(',', @WorkString);
 
	IF (@CommaPos > 0)
	THEN
	BEGIN
	-- We got a comma, so add the first string and set the WorkString to the rest
		INSERT LEFT(@WorkString, @CommaPos - 1) INTO @TempListTable ;
		SET @WorkString = RIGHT(@WorkString, LENGTH(@WorkString) - @CommaPos);
	END
 
END
 
 
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
JOIN   @TempListTable  Z
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('%' + Z.ACharacter  + '%', CommCode1) > 0)
ORDER BY C.CommunityName ASC"

Open in new window

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

http://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

0
 
CraigDeringtonAuthor Commented:
Thanks for your help with this.  Your solution worked great...
0
 
CraigDeringtonAuthor Commented:
Excellent.  Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.