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 '12 at 03:19 PM in Default

meetpavi gravatar image

meetpavi
40 4 4 5

@meetpavi First of all there is no need to use a loop. Secondly, I cannot see the comma+parentheses in your example?
Jun 26 '12 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][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
more ▼

answered Jun 26 '12 at 03:48 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

Thanks a lot Usman!
Jun 26 '12 at 08:31 PM meetpavi
@meetpavi Can you please accept the answer if it helped. This will help the future users. Thanks.
Jun 28 '12 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 '12 at 08:23 PM

meetpavi gravatar image

meetpavi
40 4 4 5

@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 '12 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1816
x5

asked: Jun 26 '12 at 03:19 PM

Seen: 1554 times

Last Updated: Jul 05 '12 at 06:10 AM