Post reply

Using IN with varchar

  • October 20, 2005 at 12:30 pm

    #91841

    Hi,

    Does anyone know how I can use the IN function in SQL Server with varchar values?

    I am trying to do this

    **********************************************************************************

    Declare @EmpNames varchar(100)

    Set @EmpNames = 'John, Mike'

    SELECT * FROM Employees WHERE EmployeeName IN (@EmpNames)

    **********************************************************************************

    Any help is appreciated

    Thanks,

    Bob

  • October 20, 2005 at 12:36 pm

    #598879

    Put a terminating comma in the list and use charindex (so that chris<>christine):

    WHERE CHARINDEX( empname+',', @namelist) > 0

    note that indexes won't be used

  • October 20, 2005 at 12:38 pm

    #598881

    Dynamic Search Conditions in T-SQL

    Arrays and Lists in SQL Server

    Neat function to do this :

    IF EXISTS (Select * from dbo.SysObjects where name = 'Numbers' and XType = 'U' and user_name(uid) = 'dbo')

    DROP TABLE Numbers

    GO

    CREATE TABLE dbo.Numbers (PkNumber int identity(1,1) primary key clustered, dude bit null)

    GO

    INSERT INTO dbo.Numbers (dude)

    SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80

    GO

    ALTER TABLE dbo.Numbers

    DROP COLUMN dude

    GO

    CREATE FUNCTION [dbo].[fnSplit_Set] (@IDS as varchar(8000), @vcDelimiter varchar(3))

    RETURNS TABLE

    WITH SCHEMABINDING

    AS

    Return

    Select dtSplitted.EachID, dtSplitted.Rank from (

    SELECT SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter),

    CHARINDEX(@vcDelimiter, @vcDelimiter + @IDs + @vcDelimiter, N.PkNumber + len(@vcDelimiter)) - N.PkNumber - len(@vcDelimiter)) as EachID

    , (LEN(SUBSTRING(@IDs, 1, N.PkNumber)) - LEN (REPLACE (SUBSTRING(@IDs, 1, N.PkNumber), ',', ''))) + 1 AS Rank

    FROM dbo.Numbers N

    WHERE SUBSTRING(@vcDelimiter + @IDs + @vcDelimiter, N.PkNumber, len(@vcDelimiter)) = @vcDelimiter

    AND PkNumber 0

    GO

    DECLARE @Ids as varchar(8000)

    SET @IDS = ''

    Select @IDS = @IDS + CAST(id as varchar(10)) + ',' from (Select TOP 10 id from dbo.SysObjects order by NEWID()) dtIds

    SET @IDS = left(@IDS, ABS(len(@IDS) - 1))

    PRINT @IDS

    Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by O.Name

    Select O.id, dtIDS.EachId, O.name, dtIDS.Rank from dbo.SysObjects O inner join (Select CAST(EachID as INT) AS EachID, Rank from dbo.fnSplit_Set (@IDS, ',')) dtIDS on O.id = dtIDS.EachID order by dtIDS.Rank

    --Dont't ever forget to cast the eachid column to the same datatype as in the join to avoid table scans.

  • October 20, 2005 at 1:33 pm

    #598910

    Remi - is this your favourite function?  It seems to keep popping up.  Must read it one day and work out what's going on

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • October 20, 2005 at 1:37 pm

    #598911

    Works well and it is easy to copy and paste .

    Besides you'd be the first to figure it out all by himself (without having already coded one).

  • October 20, 2005 at 1:45 pm

    #598914

    Easy to what?....

    The beautiful indentation that it has makes it even better


    * Noel

  • October 20, 2005 at 1:47 pm

    #598915

    Use netscape so the identation is lost... maybe someday I'll translate to html code...

  • October 20, 2005 at 1:54 pm

    #598921

    Previous version of the forumns allowed for surrounding the code between brackets but after the "upgrade" that feature was gone

     


    * Noel

  • October 20, 2005 at 2:00 pm

    #598925

    I don't want to be able to search with a comma(,)

    I want to be able to use an IN function with dynamic varchar values

    If someone enters want employees with name as "John Doe" and "Frank Smith"

    Instead of doing

    Select * FROM Employees WHERE EmpName = 'John Doe' OR EmpName = 'Frank Smith' 

    have it this was

    DECLARE @EmpName varchar(100)

    Set @EmpName = "'Frank Smith', 'John Doe'"

    SELECT * FROM Employees WHERE EmpName IN (@EmpName)

    This way I can also add Employees without the SELECT statement changing.

    Anybody know how I can accomplish this?

    Bob

  • October 20, 2005 at 2:04 pm

    #598927

    Dude read the code, it EXACTLY what it does.

  • October 20, 2005 at 2:12 pm

    #598929

    THE CHARINDEX Code doesn't seem to work. If the varchar has "'John Doe', 'Franl Smith'", it only displays records for the first one, John Doe

  • October 20, 2005 at 2:13 pm

    #598930

    Or the simple version:

    declare @strSQL nvarchar(4000)

    DECLARE @EmpName varchar(100)

    Set @EmpName = '"Frank Smith", "John Doe"'

    set @strSQL = 'SELECT * FROM Employees WHERE EmpName IN (' + @EmpName + ')'

    exec sp_executesql @strSQL

    Note: I changed around the single and double quotes to make this work.

     

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • October 20, 2005 at 2:38 pm

    #598939

    You're seriously suggesting dynamic SQL for this?

  • October 20, 2005 at 7:14 pm

    #598995

    CREATE TABLE #inList (inValue char(30) )

    insert into #inList values ('500')

    insert into #inList values ('610')

    select *

    from <yourTable>

    join #inList on <yourTable>.<yourColumn> = inValue

    drop table #inLIst

    GO

  • October 21, 2005 at 12:18 pm

    #599249

    There is even a simpler answer:

    No. 

    ...where [column name] in (@foo) ....

    does not work that way.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply