InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 17:10:25 GMT20http://www.sqlservercentral.com/Forums/Topic1366387-338-1.aspxAnother option using a table valued function. In this example I use an outer join but if you only want the code '82' values an inner join would work as well.[code="sql"]/* Table valued function for parsing a delimited array into a table */CREATE FUNCTION [dbo].[tvfParseDelimitedString] ( @S NVARCHAR(MAX) -- Delimited input string ,@Split CHAR(1) -- Delimiter used for the input string )RETURNS @Table TABLE ( [ID] INT NOT NULL IDENTITY(1,1) ,[Value] NVARCHAR(MAX) NULL ,PRIMARY KEY ([ID]) ,UNIQUE ([ID]) )BEGIN DECLARE @X XML SET @X = CONVERT(XML,'<root><s>' + REPLACE(@S,@Split,'</s><s>')+'</s></root>') INSERT INTO @Table SELECT LTRIM(T.c.value('.','NVARCHAR(MAX)')) AS [Value] FROM @X.nodes('/root/s') T (c) RETURN END/* Create a set of test data */IF OBJECT_ID('tempdb..#TempTable') IS NOT NULLDROP TABLE #TempTableCREATE TABLE #TempTable (ProdGrpCode INT)INSERT INTO #TempTableSELECT '500' UNION SELECT '510' UNION SELECT '5201' UNION SELECT '580' UNION SELECT '630' UNION SELECT '460' UNION SELECT '470' UNION SELECT '480' UNION SELECT '490' UNIONSELECT '777' UNION SELECT '888' UNION SELECT '999'/* The final query that takes the delimited input *//* and assigns the proper code */DECLARE @myVar AS NVARCHAR(50)SET @myVar = N'500, 510, 5201, 580, 630, 460, 470, 480, 490' SELECT ProdGrpCode ,(CASE WHEN ProdGrpCode = Value THEN 82 ELSE 99 END) AS CodeFROM #TempTable AS tLEFT OUTER JOIN (SELECT Value FROM dbo.tvfParseDelimitedString(@myVar,',')) AS v ON t.ProdGrpCode = v.Value[/code]Output:[font="Courier New"]ProdGrpCode Code460 82470 82480 82490 82500 82510 825201 82580 82630 82777 99888 99999 99[/font] Tue, 02 Oct 2012 11:09:00 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1366387-338-1.aspxHello,my first thought is create a temp table or var table including these relations, then join.[code="sql"]CREATE TABLE #T (ProdGrpCode varchar(10), ResultCode VARCHAR(10))INSERT INTO #TSELECT '500', '82' UNION SELECT '510', '82' UNION SELECT '5201', '82' UNION SELECT '580', '82' UNION SELECT '630', '82' UNION SELECT '460', '82' UNION SELECT '470', '82' UNION SELECT '480', '82' UNION SELECT '490', '82'[/code]Now, your reference[code="sql"]casewhen ProdGrpCode in ('500','510','5201','580','630','460','470','480','490') then '82' ....[/code]is replaced simply by ResultCode.FrancescTue, 02 Oct 2012 03:48:19 GMTfrfernanhttp://www.sqlservercentral.com/Forums/Topic1366387-338-1.aspxhi Spin,I would look at [url=http://www.sommarskog.se/dyn-search-2008.html]Sommarskogs homepage[/url] for a detailed description of the problem.Best regards,HenrikTue, 02 Oct 2012 02:04:26 GMThenrik staun poulsenhttp://www.sqlservercentral.com/Forums/Topic1366387-338-1.aspxThis may also work for you[code="sql"]casewhen ', ' + @myVar + ', ' like '%, ' + ProdGrpCode + ', %' then '82' [/code]Mon, 01 Oct 2012 07:45:47 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1366387-338-1.aspx[quote][b]GilaMonster (10/1/2012)[/b][hr]I wouldn't use that function. The loops will make it very slow.Try this one: [url]http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url][/quote]I hate loops as well, but it will be ok if used for splitting a single list of values. Actually, properly written loop splitter will outperform Jeff Moden tally-table based split for a single list, however, if you need to use the split function for a set of rows, you better use mentioned Jeff's one.Mon, 01 Oct 2012 07:40:31 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1366387-338-1.aspxI wouldn't use that function. The loops will make it very slow.Try this one: [url]http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]Mon, 01 Oct 2012 06:26:01 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1366387-338-1.aspxAlong the lines of what GilaMonster said I pass strings that I need to use in "Where in" quite a bit. This is what I use:I have this funciton that I got off the web somewhere:[code]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[DelimitedStringToTable](@String varchar(8000), @Delimiter char(1)) returns @temptable TABLE (items varchar(8000)) as begin declare @idx int declare @slice varchar(8000) select @idx = 1 if len(@String)<1 or @String is null return while @idx!= 0 begin set @idx = charindex(@Delimiter,@String) if @idx!=0 set @slice = left(@String,@idx - 1) else set @slice = @String if(len(@slice)>0) insert into @temptable(Items) values(@slice) set @String = right(@String,len(@String) - @idx) if len(@String) = 0 break end return endGO[/code]I use it like this:declare @myVar varchar(50)set @myVar = '500, 510, 5201, 580, 630, 460, 470, 480, 490'when ProdGrpCode in (Select * from dbo.DelimitedStringToTable(@myVar,',')The first parameter is the string to parse and the second in the character that is the delimiter. It’s the equivilant of selecting where in a sub select of a table.Mon, 01 Oct 2012 05:17:48 GMTtyson.pricehttp://www.sqlservercentral.com/Forums/Topic1366387-338-1.aspxOr you could use a string splitter function and then join to the results of that. Less risky than dynamic SQL (not vulnerable to SQL injection)Mon, 01 Oct 2012 02:47:36 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1366387-338-1.aspxDynamic SQl would enable you to do this.Have a look at a previous discussion for a similair example: [url]http://www.sqlservercentral.com/Forums/Topic1261985-145-1.aspx[/url].Mon, 01 Oct 2012 02:44:33 GMTBrainDonorhttp://www.sqlservercentral.com/Forums/Topic1366387-338-1.aspxhii have a query which i need to group some codes into one code.so...[code]casewhen ProdGrpCode in ('500','510','5201','580','630','460','470','480','490') then '82' ....[/code]is there a way i can add them to a variable and use that instead because i need to repeat the lines elsewhere.i tried [code]declare @myVar as varchar(50)set @myVar = '500, 510, 5201, 580, 630, 460, 470, 480, 490'casewhen ProdGrpCode in (@myVar) then '82' [/code]but it doesn't work. It searches to match the entire string not each individual code. I don't really want to have to create a variable for each code.ThanksMon, 01 Oct 2012 02:27:03 GMTspin