Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MSSQL IN statement with comma-delimited returns

Posted on 2010-11-16
12
Medium Priority
?
232 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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Accepted Solution

by:
lomo74 earned 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

783 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