Solved

MSSQL IN statement with comma-delimited returns

Posted on 2010-11-16
12
223 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now