asked
Combining two Tables with different data type
I have a string which has paranthesis and comma, I have to input this to a stored procedure and forecast qty Since i was not sure how to do that, i have 2 list of parameters one for part id and other for qty as you can see below. I have created 2 temp tables where the value in the string gets parsed. i would either like to know how to parse the string with both comma and paranthesis or if i can combine the two temp tables which has totally different datatypes *** Sript ***** SET ARITHABORT OFF GO SET ANSI_WARNINGS OFF GO declare @partlist varchar(500) declare @partqtylist varchar(200) declare @partqty as varchar(200) set @partlist = 'RPB70-1716,RPB70-001,RPB70-1621' set @partqtylist = '100,150,160' BEGIN SET NOCOUNT ON create table #temptable (partid varchar(500)) DECLARE @partid varchar(500) DECLARE @Pos int SET @partlist = LTRIM(RTRIM(@partlist))+ ',' -- check for spaces between delimiters SET @Pos = CHARINDEX(',', @partlist, 1) -- check the number of delimiters IF REPLACE(@partlist, ',', '') <> '' -- if theres is a delimiter in the part list replace it with '' BEGIN WHILE @Pos > 0 -- while the number of delimiters more than 0 BEGIN SET @partid = LTRIM(RTRIM(LEFT(@partlist, @Pos - 1))) -- Select the leftmost part from the list.-- IF @partid <> '' BEGIN INSERT INTO #temptable(partid) VALUES (@partid) END SET @partlist = RIGHT(@partlist, LEN(@partlist) - @Pos) SET @Pos = CHARINDEX(',', @partlist, 1) END END create table #temptable1(partqty varchar(200)) DECLARE @Pos1 int SET @partqtylist = LTRIM(RTRIM(@partqtylist))+ ',' -- check for spaces between delimiters SET @Pos1 = CHARINDEX(',', @partqtylist, 1) -- check the number of delimiters IF REPLACE(@partqtylist, ',', '') <> '' -- if theres is a delimiter in the part list replace it with '' BEGIN WHILE @Pos1 > 0 -- while the number of delimiters more than 0 BEGIN SET @partqty = LTRIM(RTRIM(LEFT(@partqtylist, @Pos1 - 1))) -- Select the leftmost part from the list.-- IF @partqty <> '' BEGIN INSERT INTO #temptable1(partqty) VALUES (@partqty) END SET @partqtylist = RIGHT(@partqtylist, LEN(@partqtylist) - @Pos1) SET @Pos1 = CHARINDEX(',', @partqtylist, 1) END END END select #temptable.partid,#temptable1.partqty into #temp3 from #temptable,#temptable1 drop table #temp3 drop table #temptable drop table #temptable1
@meetpavi First of all there is no need to use a loop. Secondly, I cannot see the comma+parentheses in your example?
As per given example, this is how it can be done (I have used a function which would split the strings using cteTally table). But mind you that the function limitation is a string upto 8000 characters. If the string can be greater than that then you can use @Oleg 's solution mentioned in this [question][1] USE [tempdb] GO CREATE FUNCTION [dbo].[DelimitedSplit8K] --===== Define I/O parameters (@pString VARCHAR(8000), @pDelimiter VARCHAR(1)/*===MADE IT VARCHAR TO AVOID IMPLICIT CONVERSION WHILE COMPARING====*/ ) RETURNS TABLE WITH SCHEMABINDING AS RETURN --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000... -- enough to cover VARCHAR(8000) WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front -- for both a performance gain and prevention of accidental "overruns" SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ), cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter) /*====FIRST ITEM HANDLED AT THE END======*/ --SELECT 1 UNION ALL -- does away with 0 base CTE, and the OR condition in one go! /*=======================================*/ SELECT N+1 FROM cteTally WHERE SUBSTRING(@pString ,N,1) COLLATE Latin1_General_BIN = @pDelimiter COLLATE Latin1_General_BIN ), cteLen(N1,L1) AS(--==== Return start and length (for use in substring) SELECT N1, ISNULL(NULLIF(CHARINDEX(@pDelimiter COLLATE Latin1_General_BIN,@pString COLLATE Latin1_General_BIN,N1),0) - N1,8000) FROM cteStart ) --===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. /*====FIRST ITEM HANDLING===============*/ SELECT ItemNumber = 0, Item = SUBSTRING(@pString , 1, ISNULL(NULLIF(CHARINDEX(@pDelimiter COLLATE Latin1_General_BIN,@pString COLLATE Latin1_General_BIN),0)-1, 8000)) UNION ALL SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY N1), Item = SUBSTRING(@pString , N1, L1) FROM cteLen ; GO DECLARE @partlist VARCHAR(500) DECLARE @partqtylist VARCHAR(200) DECLARE @partqty AS VARCHAR(200) SET @partlist = 'RPB70-1716,RPB70-001,RPB70-1621' SET @partqtylist = '100,150,160' BEGIN SET NOCOUNT ON SELECT PartList.Item Part, PartQtyList.Item PartQty FROM [dbo].[DelimitedSplit8K](@partlist, ',') PartList LEFT JOIN [dbo].[DelimitedSplit8K](@partqtylist, ',') PartQtyList ON [PartList].[ItemNumber] = [PartQtyList].[ItemNumber] END [1]:
http://ask.sqlservercentral.com/questions/88772/has-anyone-tried-variations-on-8k-splitter.html
@meetpavi Can you please accept the answer if it helped. This will help the future users. Thanks.
Hi Can some one tell me the reason fr the following error? I use the above code and linked it to a stored procedure to use it in a crystal report. I get an error saying Query Engine Error: 'ADO Error Code: 0x Source: Microsoft OLE DB Provider for SQL Server Description: Error Converting data type varchar to numeric. SQL State:22018 Native Error: ' Thank You So much for your help.
@meetpavi I cannot see any numeric column usage in the above code. So I guess you need to provide more details like are you mapping something to a numeric column?
8 People are following this question.
