Sql Server Replace function inside of IN operator -

i trying build dynamic sql query.

i have field string of pipe delimited guids.

i want execute query finds users guids in field.

in example below have added couple of dummy guids examples. second query works want to. first query replace function not working. if execute replace select return have in second query expect return same results. can please me out missing here? thanks.

select * cms_user  convert(varchar(36),userguid) in(     select '''' + replace('6415b8ce-8072-4bcd-8e48-9d7178b826b7|aedd6e3f-61c1-46cf-b2d4-750180036fff','|',''',''') + ''''     )   select *  cms_user  convert(varchar(36),userguid) in('6415b8ce-8072-4bcd-8e48-9d7178b826b7','aedd6e3f-61c1-46cf-b2d4-750180036fff') 

update: here sqlfiddle queries further show doing.

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

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

select pipe delimited guids trying replaced in subquery http://sqlfiddle.com/#!3/a5e426/7

subquery select show getting want http://sqlfiddle.com/#!3/a5e426/8

i think rest of still need little more information on trying query return. (why hardcoding values unacceptable). guess need dynamic query can account multiple users. in query below should able replace set number of guid's.

i dislike using exec command seems 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 * cms_user userguid in(' + @tempid + ')  ') 

edit: mentioned getting guid's table. why not reference table directly?

select * cms_user  userguid in(select userguid othertable)