Solved

MSSQL IN statement with comma-delimited returns

Posted on 2010-11-16
12
229 Views
Last Modified: 2012-05-10
I have an SQL query that has a statement like

 where regionid in (tblpeople.region)

where the results from tblpeople.region could be something like 3913 or something like 3913, 3914.

It works just fine when it is a single variable but multiples gives me the error  Conversion failed when converting the varchar value '3913,3914' to data type int.    How do I go about fixing this?  
0
Comment
Question by:digitalwise
[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
  • 3
  • 3
  • +1
12 Comments
 
LVL 6

Expert Comment

by:Codebot
ID: 34149360
remove single quotes or place quote around each value.
3913,3914
or
'3913','3914'
0
 
LVL 32

Expert Comment

by:Erick37
ID: 34149366
Several ways:

1) create dynamic SQL so the actual query statement looks like this
'where regionid in (3913,3914)'

2) change the code so that the query selects each region id as a separate row
where regionid in (select regionid from sometable where ...)

3) create a table function which accepts your comma delimited string and splits it into rows:
where regionid in (select value from dbo.uf_YourSplit(tblpeople.region))


0
 

Author Comment

by:digitalwise
ID: 34149447
It is pulling the information from the actual table field and I don't want separate rows - the whole point is to pull the actual region names when the regionid is stored in the region field so that I can present the information back as what has been picked WITHOUT having to do a lot of extra queries.    This is the complete query.   I am using this in ColdFusion if that helps.


select firstname, lastname, title, personid, nl, dbo.GetProxy(tblpeople.entityid) as Proxy, 
reglist, (SELECT regionname + ', ' AS [text()]
    FROM   dt_regions
    where regionid in (tblpeople.region)
    FOR XML PATH('')) as regiontoshow,  (SELECT regionname + ', ' AS [text()]
    FROM   dt_regions
    where regionid in (tblpeople.chapter)
    FOR XML PATH('')) as chaptertoshow, chapter from tblpeople
	where companyid = 914815
	and peopletype = 2
    
    order by lastname

Open in new window

0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 8

Accepted Solution

by:
lomo74 earned 500 total points
ID: 34149580
select *
from yourtable
where exists (
select *
from tblpeople
where (',' + replace(tblpeople.region, ' ', '') + ',') like ('%,' + cast(yourtable.regionid as varchar) + ',%')
)

basically the operator becomes, eg.
',3913,3914,'  like  '%,3913,%'  which matches -> record extracted
0
 
LVL 8

Expert Comment

by:lomo74
ID: 34149630
sorry I posted just before you submitted the full query.
here it is

select firstname, lastname, title, personid, nl, dbo.GetProxy(tblpeople.entityid) as Proxy, 
reglist, (SELECT regionname + ', ' AS [text()]
    FROM   dt_regions
    where exists (
		select *
		from tblpeople
		where (',' + replace(tblpeople.region, ' ', '') + ',') like ('%,' + cast(dt_regions.regionid as varchar) + ',%')
    )
    FOR XML PATH('')) as regiontoshow,  (SELECT regionname + ', ' AS [text()]
    FROM   dt_regions
    where regionid in (tblpeople.chapter)
    FOR XML PATH('')) as chaptertoshow, chapter from tblpeople
	where companyid = 914815
	and peopletype = 2
    
    order by lastname

Open in new window

0
 
LVL 32

Expert Comment

by:Erick37
ID: 34149802
What is the data type of your column tblpeople.region?

I'm assuming varchar or text since the result can be '3913, 3914'

In which case use the split user function:

select
      firstname,
      lastname,
      title,
      personid,
      nl,
      dbo.GetProxy(tblpeople.entityid) as Proxy,
      reglist,
      (SELECT regionname + ', ' AS [text()]
            FROM   dt_regions
            where regionid in (dbo.uf_Split2Int(tblpeople.region,','))
            FOR XML PATH('')) as regiontoshow,  
      (SELECT regionname + ', ' AS [text()]
            FROM   dt_regions
            where regionid in (dbo.uf_Split2Int(tblpeople.chapter,','))
            FOR XML PATH('')) as chaptertoshow,
      chapter
from tblpeople
where companyid = 914815
and peopletype = 2
order by lastname
CREATE FUNCTION [dbo].[uf_Split2Int] 
(
	@cList nvarchar(2000)
	,@cDelimiter nvarchar(5)
)  
RETURNS @SplitTable TABLE 
(
	nFieldValue int
) 
AS  
BEGIN 
	WHILE (CHARINDEX( @cDelimiter, @cList ) > 0)
	BEGIN
		INSERT INTO @SplitTable(nFieldValue)
		SELECT 
			CAST(LTRIM(RTRIM(SUBSTRING( @cList, 1, CHARINDEX( @cDelimiter, @cList ) - 1 ))) as int)

		SET @cList = SUBSTRING( @cList, CHARINDEX( @cDelimiter, @cList ) + LEN( @cDelimiter ), LEN(@cList) )
	END

	INSERT INTO @SplitTable (nFieldValue)
	SELECT LTRIM(RTRIM(@cList))

	RETURN
END

GO

Open in new window

0
 
LVL 32

Expert Comment

by:Erick37
ID: 34149905
Correction: forgot to add the 'select nFieldValue from'

select
      firstname,
      lastname,
      title,
      personid,
      nl,
      dbo.GetProxy(tblpeople.entityid) as Proxy,
      reglist,
      (SELECT regionname + ', ' AS [text()]
            FROM   dt_regions
            where regionid in (select nFieldValue from dbo.uf_Split2Int(tblpeople.region,','))
            FOR XML PATH('')) as regiontoshow,  
      (SELECT regionname + ', ' AS [text()]
            FROM   dt_regions
            where regionid in (select nFieldValue from dbo.uf_Split2Int(tblpeople.chapter,','))
            FOR XML PATH('')) as chaptertoshow,
      chapter
from tblpeople
where companyid = 914815
and peopletype = 2
order by lastname
0
 

Author Comment

by:digitalwise
ID: 34150502
lomo74 - this pulls back ALL of the region names from the region table and not just the ones that are in the person's record...    
0
 

Author Comment

by:digitalwise
ID: 34150780
erick - I get a Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near '.'.
0
 

Author Comment

by:digitalwise
ID: 34151807
OK - lomo74 got pretty close - I had to do the same thing for the chapter but it worked (your first version)


select firstname, lastname, title, personid, nl, dbo.GetProxy(tblpeople.entityid) as Proxy, 
reglist, (SELECT regionname + ', ' AS [text()]
    FROM   dt_regions
   where (',' + replace(tblpeople.region, ' ', '') + ',') 
   like ('%,' + cast(dt_regions.regionid as varchar) + ',%')

    FOR XML PATH('')) as regiontoshow,  (SELECT regionname + ', ' AS [text()]
    FROM   dt_regions
    where (',' + replace(tblpeople.chapter, ' ', '') + ',') 
   like ('%,' + cast(dt_regions.regionid as varchar) + ',%')
    FOR XML PATH('')) as chaptertoshow, chapter from tblpeople
	where companyid = 914815
	and peopletype = 2
    
    order by lastname

Open in new window

0
 

Author Closing Comment

by:digitalwise
ID: 34151824
I added it to my code - the "full solution" didn't work but I was able to apply it properly to the piece i needed.
0
 
LVL 8

Expert Comment

by:lomo74
ID: 34153024
sorry for delay I went to bed :-)
glad to hear you solved the second problem (the chapter) on your own.
hope you can fix the full solution now!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

627 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