x

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

more ▼

asked Jun 26, 2012 at 03:19 PM in Default

avatar image

meetpavi
50 4 4 7

@meetpavi First of all there is no need to use a loop. Secondly, I cannot see the comma+parentheses in your example?

Jun 26, 2012 at 03:38 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

 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


more ▼

answered Jun 26, 2012 at 03:48 PM

avatar image

Usman Butt
14k 6 13 21

Thanks a lot Usman!

Jun 26, 2012 at 08:31 PM meetpavi

@meetpavi Can you please accept the answer if it helped. This will help the future users. Thanks.

Jun 28, 2012 at 01:05 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jul 02, 2012 at 08:23 PM

avatar image

meetpavi
50 4 4 7

@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?

Jul 05, 2012 at 06:10 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2076
x7

asked: Jun 26, 2012 at 03:19 PM

Seen: 2139 times

Last Updated: Jul 05, 2012 at 06:10 AM

Copyright 2016 Redgate Software. Privacy Policy