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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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