IN() using VARCHAR values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

IN() using VARCHAR values

Author

Topic

knox203
Starting Member

31 Posts

Posted - 2009-10-30 : 14:39:51

Hey everyone,

I would like to use delimited values (separated by commas, i.e. - '19, 237, 381') inside of an "IN()" statement... but instead am getting the following error when I run my query:

Conversion failed when converting the varchar value '19, 237, 381' to data type int.

The comma delimited data is being generated by a separate query via "FOR XML PATH()", then I'm trying to use my "CASE" condition from those comma delimited values with the "IN()" statement.

Can anyone help me make the delimited VARCHAR variable compatible with the "IN()" statement?

Did I make anyone confused? lol...

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-10-30 : 14:48:13

search this site for Split() Function.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-30 : 14:48:33

it has to be ('19','237','381') and you can't do IN (@var) either.

Jim

Everyday I learn something that somebody else already knew

knox203
Starting Member

31 Posts

Posted - 2009-10-30 : 14:57:29

Thanks guys for your responses.

russell: I'll do some searching, thanks for the tip.

- Adam

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-10-30 : 16:02:04

It looks like you are using the wrong approach.

You more than likley can simply join to your result sets from the xml query and make this far more efficiant.

Please post your FOR XML PATH query and also the query you are looking to use the IN statement with.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

knox203
Starting Member

31 Posts

Posted - 2009-10-30 : 18:12:57

[code]SELECT DATEDIFF(ss, MIN(DATEADD(hh, -7, C.Date)), MAX(DATEADD(hh, -7, C.Date))) AS [Seconds],
REPLACE(RTRIM(( SELECT CONVERT(VARCHAR, [UserId]) + ' '
FROM LcsLog.dbo.ConferenceMessageRecipientList
WHERE ([MessageId] = (
SELECT MessageId
FROM LcsLog.dbo.ConferenceMessages
WHERE (DATEADD(hh, -7, Date) = MIN(DATEADD(hh, -7, C.Date)))
)
) FOR XML PATH ('')
)),' ',',') AS [To]
FROM LcsLog.dbo.ConferenceMessages AS C INNER JOIN
LcsLog.dbo.Users AS U ON C.FromId = U.UserId
WHERE (DATEADD(hh, -7, C.SessionIdTime) BETWEEN CONVERT(VARCHAR, GETDATE(), 101) AND CONVERT(VARCHAR, DATEADD(d, 1, GETDATE()), 101))
GROUP BY DATEADD(hh, -7, C.SessionIdTime)
HAVING DATEDIFF(ss, MIN(DATEADD(hh, -7, C.Date)), MAX(DATEADD(hh, -7, C.Date))) <> 0[/code]
Sorry about it looking so fragmented. This query is returning two columns; the total Session time (in seconds) and a list of recipients. The process is as follows; first it's searching through a chat database, I'm grouping the Session then grabbing the GUID for the first message sent in that Session -- each message has it's own GUID. I'm then taking that GUID and searching through another table for all recipients for that GUID and concatenating them with the 'FOR XML' and putting that (UserID) value (i.e. - '16, 24, 115, 486') next to the Session time.

Results look like:
[code]SessionTime Recipients
1139 19, 237, 381
46 26
788 3, 11, 26, 86, 89, 115
211 3, 26, 86
1921 3, 26, 86[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-31 : 02:51:27

use

','+@var+',' like '%,'+cast(col as varchar(10)))+',%'

Madhivanan

Failing to plan is Planning to fail

Pradip
Starting Member

32 Posts

Posted - 2009-10-31 : 06:42:59

If you compare varchar then ('19', '237', '381') will be correct otherwise if compare int then (19, 237, 381) will work fine

pradipjain

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-31 : 08:05:46

quote:
Originally posted by Pradip

If you compare varchar then ('19', '237', '381') will be correct otherwise if compare int then (19, 237, 381) will work fine

pradipjain

Did you read the question fully?

Madhivanan

Failing to plan is Planning to fail

knox203
Starting Member

31 Posts

Posted - 2009-11-03 : 16:20:30

Sorry about the delayed response, life's been pretty crazy lately, I'm sure you all can relate!

madhivanan: I'm not quite to sure where to use that. I can see what you're going towards, would I place that condition in my main query, or one of the nested queries?

Thank you!

- Adam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-04 : 01:44:44

quote:
Originally posted by knox203

Sorry about the delayed response, life's been pretty crazy lately, I'm sure you all can relate!

madhivanan: I'm not quite to sure where to use that. I can see what you're going towards, would I place that condition in my main query, or one of the nested queries?

Thank you!

- Adam

Put it in a main query

Madhivanan

Failing to plan is Planning to fail

knox203
Starting Member

31 Posts

Posted - 2009-11-09 : 13:02:05

I had to re-think my query a little bit, but in conjunction with your code it worked beautifully! Thanks for the help, madhivanan. Much appreciated!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-10 : 01:58:43

quote:
Originally posted by knox203

I had to re-think my query a little bit, but in conjunction with your code it worked beautifully! Thanks for the help, madhivanan. Much appreciated!

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources