Solved

MSSQL IN statement with comma-delimited returns

Posted on 2010-11-16
12
225 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

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.

Question has a verified solution.

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

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…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

830 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