Solved

Help with Sql 'In '

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

626 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