Sql Server Replace function inside of IN operator

Question

I am trying to build a dynamic sql query.

I have a field which is a string of pipe delimited Guids.

I want to execute a query that finds all Users with the Guids in this field.

In the example below I have just added a couple of dummy GUIDs for examples. My second query works as I want it to. My first query with the REPLACE function is not working. If I execute just the Replace Select it does return what I have in the second query so I would expect it to return the same results. Can someone please help me out with what I am missing here? Thanks.

select * from CMS_User 
WHERE Convert(varchar(36),UserGUID) IN(
    select '''' + REPLACE('6415B8CE-8072-4BCD-8E48-9D7178B826B7|AEDD6E3F-61C1-46CF-B2D4-750180036FFF','|',''',''') + ''''
    )


select * 
from CMS_User 
WHERE Convert(varchar(36),UserGUID) IN('6415B8CE-8072-4BCD-8E48-9D7178B826B7','AEDD6E3F-61C1-46CF-B2D4-750180036FFF')

UPDATE: Here are some SqlFiddle Queries to further show what I am doing.

Select all shows 2 records http://sqlfiddle.com/#!3/a5e426/5

Select with hardcoded subquery http://sqlfiddle.com/#!3/a5e426/6

Select with pipe delimited Guids trying to be replaced for the IN subquery http://sqlfiddle.com/#!3/a5e426/7

Subquery select to show I am getting what I want http://sqlfiddle.com/#!3/a5e426/8

How do you "*only have access to the WHERE of the query*" are you pasting this string in to a text box and running it, or is this being scripted in some other language and being passed off to Sql Server (ie: Can pre-prossing happen?)? Also define "*My first query with the REPLACE function is not working.*" Is it throwing a error, are you getting no results, are you getting results but they are the wrong results? Unless you set something like [SqlFiddle](http://sqlfiddle.com/) up for us with example data we can't see what is happening and you need to tell us. — Scott Chamberlain, Aug 19 '13 at 19:12
I should remove that only access to Where. That is my end case, but this is actually happening right in Management Studio. Sorry, "not working" meaning it is not returning any empty data set. Where as the second query has a data set with two results. I'll try out SqlFiddle. — Dukebaby, Aug 19 '13 at 19:38
What version of SQL are you using, there are some potential solutions ([Table Valued Parameters](http://technet.microsoft.com/en-us/library/bb510489.aspx)) but are only available in sql2008 or newer. — Scott Chamberlain, Aug 19 '13 at 20:13
And to help you understand what your problem is (if you figure out how to solve it before anyone answers feel free to post an answer to your own question), the query you are running in your non working version [is really `WHERE Convert(varchar(36),UserGUID) IN('''6415B8CE-8072-4BCD-8E48-9D7178B826B7'',''AEDD6E3F-61C1-46CF-B2D4-750180036FFF''')`](http://sqlfiddle.com/#!3/a5e426/9) — Scott Chamberlain, Aug 19 '13 at 20:15

orgtigger · Answer 1 · 2013-08-20T15:50:51.887

I think the rest of us still need a little more information on exactly what you are trying to get your query to return. (Why is hardcoding values unacceptable). My guess is that you need a dynamic query that can account for multiple users. In the query below you should be able to replace the set with any number of GUID's.

I dislike using the exec command but it seems that is what works here

declare @tempID varchar(max)
set @tempID = '6415B8CE-8072-4BCD-8E48-9D7178B826B7|AEDD6E3F-61C1-46CF-B2D4-750180036FFF'
set @tempID =  char(39) + replace (@tempID
                    ,'|', CHar(39) + char(44) + Char(39)) + char(39)

exec(' select * from CMS_User WHERE UserGUID IN(' + @tempID + ')  ')

EDIT: You mentioned getting the guid's from another table. Why not reference that table directly?

Select * from CMS_User 
WHERE UserGUID in(select UserGUID from othertable)

You're right, I need a dynamic query. I am getting the UserGUIDs from a field in a different table. So this obviously changes per record. You example may work, but I can only work inside the WHERE clause (CMS Limitations) so what you haven't won't help me, but thank you. — Dukebaby, Aug 19 '13 at 22:09

Sql Server Replace function inside of IN operator

1 Answers1