Solved

Help with Sql 'In '

Posted on 2013-11-11
5
301 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:ScottPletcher
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now