Using IN with varchar
-
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
-
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
-
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.
-
Remi - is this your favourite function? It seems to keep popping up. Must read it one day and work out what's going on
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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).
-
Easy to what?....
The beautiful indentation that it has makes it even better
* Noel -
Use netscape so the identation is lost... maybe someday I'll translate to html code...
-
Previous version of the forumns allowed for surrounding the code between
* Noel -
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
-
Dude read the code, it EXACTLY what it does.
-
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
-
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
You're seriously suggesting dynamic SQL for this?
-
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
-
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