Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with Sql 'In '

Posted on 2013-11-11
5
Medium Priority
?
315 Views
Last Modified: 2013-11-16
Hi experts,

I am having a Sql table like

HId HName CityId(int)  NearByCityId(varchar)
1      xxxx       2              3,5,6
2      rrrrr        4              null
3      sdfd        3              5,6

Now when user passes CityId its straight forward.
But now I need to lookup in NearByCityId along with cityId.

e.g, when CityId 3 is passed then it should be searched in CityId and NearByCityId columns

where CityId = 3 or NearByCityId contains '3'

Stucked here. Your help is really appreciated.
Thanks in advance.
0
Comment
Question by:johny_bravo1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 11

Accepted Solution

by:
Louis01 earned 2000 total points
ID: 39638456
Try this?

select *
  from YourSqlTable t1
 where t1.CityId = 3 
       or '3' in (select [data] from [dbo].[fn_StringToTable (t1.NearByCityId, ','))

Open in new window

Function: fn_StringToTable
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_StringToTable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_StringToTable]
GO

CREATE FUNCTION [dbo].[fn_StringToTable]
  (
   @string VARCHAR(MAX)
 , @delimiter CHAR(1)
  )
RETURNS @output TABLE (data VARCHAR(4000))
  BEGIN
    DECLARE
      @start INT
    , @end INT
    SELECT
      @start = 1
    , @end = CHARINDEX(@delimiter, @string)
    WHILE @start < LEN(@string) + 1 
      BEGIN
        IF @end = 0 
          SET @end = LEN(@string) + 1

        INSERT  INTO @output
                (data)
        VALUES
                (SUBSTRING(@string, @start, @end - @start))
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)
      END
    RETURN
  END
GO

Open in new window

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 39638499
SELECT * FROM YourTable 
	WHERE	CityId = 3
		OR	NearByCityId LIKE '3,%'
		OR	NearByCityId LIKE '%,3,%'
		OR	NearByCityId LIKE '%,3'		
		OR	NearByCityId LIKE '3'

Open in new window

     

regards
raj
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39640235
The table design is faulty.  You need to create a separate table for NearByCityIds:


HId NearByCityId(varchar)
1      3
1      5
1      6
3      5
3      6
0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 39645773
If you can split out the NearbyCityIDs, you should as recommended by ScottPletcher.  However, if some business requirement forces you to deal with the field containing a list of values, a function to split out the string as recommended by Louis01 is the faster alternative.

However, the splitting function Louis01 uses is not the fastest.  Based on extensive testing, a tally table based splitter works best.

A tally table is simply a list of sequential items, such as positive integers (1,2,3,...N) or dates (20010101, 20010102, 20010103, ...).  The function below uses a tally table of integers (in my environment, simply named 'Tally') as pointers into the string to split the string quickly.  

CREATE FUNCTION [dbo].[Split2Table](
    @Data varchar(Max),
    @delim char(1)=','
    ) 
RETURNS @t table (
    Data varchar(255)
    ) 
AS
BEGIN
    SET @Data = @delim +@Data + @delim  -- simplifies the splitting code

	-- Join the Tally table to the string at the character level and
	-- when we find a comma, SELECT what is between that comma and 
	-- the next comma into the table for return
	
	INSERT INTO @t
	SELECT 
		SUBSTRING(@Data,N+1,CHARINDEX(@delim,@Data,N+1)-N-1) Data
	FROM Operations.dbo.Tally WITH(NOLOCK)
	WHERE N < LEN(@Data)
		AND SUBSTRING(@Data,N,1) = @delim --Notice how we find the comma
		
	RETURN
END

Open in new window

There are many different ways to create and populate a tally table.  Here is my preferred method:

CREATE TABLE Tally (N int PRIMARY KEY CLUSTERED)

INSERT INTO Tally Values(1)  -- prime the table

DECLARE @loop int, 
    @m int
SET @loop = 0
WHILE @loop < 13 -- This deserves some explanation
/*
The loop value represents a value of 2^N.  2^0 = 1, 2^1=2, 2^2=4, 2^3=8, etc.  If the while loop read 'WHILE LOOP < 0, then the table would have only one value. If it read WHILE LOOP < 3, it would have 8 values at the end.  The loop above gives 2^13 values, or 8,192 values from 1 to 8192.
*/
BEGIN
    SELECT @m = Max(N) from Tally

    INSERT INTO Tally
    SELECT N + @M

    SET @loop = @loop + 1
END

Open in new window

My tally table at work contains 2^20 entries, because some processes require parsing million character documents provided to us.
0
 
LVL 8

Author Closing Comment

by:johny_bravo1
ID: 39652995
Thanks it works really nice. Sorry experts to I took time to accept this answer
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question