Solved

Help with Sql 'In '

Posted on 2013-11-11
5
308 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

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…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

770 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