Solved

Help with Sql 'In '

Posted on 2013-11-11
5
310 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
5 Comments
 
LVL 11

Accepted Solution

by:
Louis01 earned 500 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:bhess1
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

820 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