Split function input be column of a table
-
I need help in joining 2 tables using a Split function
TableA
ID
Nodes
LT
M1
1;2;3
4
M2
4;5;6
5
TableB
ID
Desc
Qty
1
Desc of 1
2
2
Desc of 2
13
3
Desc of 3
43
4
Desc of 4
52
5
Desc of 5
66
6
Desc of 6
77
The output should be
TableAID
LT
TableBID
Desc
Qty
M1
4
1
Desc of 1
2
M1
4
2
Desc of 2
13
M1
4
3
Desc of 3
43
M2
5
4
Desc of 4
52
M2
5
5
Desc of 5
66
M2
5
6
Desc of 6
77
The Split Function takes in parameters of string of integers (Nodes column) and a delimiter and returns a table with elements. The elements of the split function joins to ID column of TableB.
Thanks
-
You can use PATINDEX or CHARINDEX to get all the values from Nodes in Table A.
-
Using Charindex and Substring functions will return just a single value. The split function returns a table
If I do a Split function as select * from dbo.split('4;5;6',';') I get back 3 rows as
ID Value
1 4
2 5
3 6
-
eek I'm gonna say the words " only a cursor can solve this"; while I know cursor is the antithesis to all things SSC, sometimes you have to use the devil's tool:
create the expected table, and in the cursor insert them into the table:
create table #RESULTS (TableAID varchar(30), @Node varchar(100), @Qty int)
declare
@TableAID varchar(30),
@Nodes varchar(8000),
@Qty int
declare c1 cursor for select TableAID,Nodes,LT from TABLEA
open c1
fetch next from c1 into @TableAID,@Nodes,@Qty
While @@fetch_status <> -1
INSERT INTO #RESULTS SELECT @TABLEAID,@LT,Element from dbo.split(@NODES)
fetch next from c1 into @TableAID,@Nodes,@Qty
end
close c1
deallocate c1
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Cursor?
Do you want your mouth washed out with soap?All you need is:
Select A.ID As TableAID, A.LT, B.ID As TableBID, B.[Desc], B.Qty
From
TableA A
Inner
Join TableB B On B.ID IN (Select ParsedValue From dbo.FN_SplitString(A.Nodes, ';'))
My test code:
Create
Table TableA (ID varchar(2), Nodes varchar(5), LT int)
Create
Table TableB (ID int, [Desc] varchar(20), Qty int)
Insert
Into TableA Values ('M1', '1;2;3', 4)
Insert
Into TableA Values ('M2', '4;5;6', 5)
Insert
Into TableB Values (1, 'Desc of 1', 2)
Insert
Into TableB Values (2, 'Desc of 2', 13)
Insert
Into TableB Values (3, 'Desc of 3', 43)
Insert
Into TableB Values (4, 'Desc of 4', 52)
Insert
Into TableB Values (5, 'Desc of 5', 66)
Insert
Into TableB Values (6, 'Desc of 6', 77)
Select
A.ID As TableAID, A.LT, B.ID As TableBID, B.[Desc], B.Qty
From
TableA A
Inner
Join TableB B On B.ID IN (Select ParsedValue From dbo.FN_SplitString(A.Nodes, ';'))
Drop
Table TableA
Drop
Table TableB
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
* EDIT * Just noticed the original poster has a split function! so I guess you can ignore the next paragraph!
All good and well Robert, and am in agreement with you, apart from the fact that as far as I know fn_SplitString is not either a SQL 2000 nor 2005 system function, and therefore you would have to add this as well - and to do that you need create privelages for functions (this function and several like it are documented by several people so will not add it here).
-
curses on cursors! I am appropriately humbled. Thanks for putting me back on the path of enlightenment, Robert; I just couldn't "see" the subselect; nice solution.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I also looked at the split function. It is not supported by Sql server 2000 and it was written using CHARINDEX.
I don't like the idea that using something that is not supported by SQL Server 2000 because when other people did not know where you got the function.
It is very easy to write the split function yourself using CHARINDEX in your procedure. This way it is clear to all the other developers.
my 2 cents.
-
function not supported by sql 2000....so you'll write your own function...um...that is also not supported?
I think you mean simply not explicitly written by microsoft...technically all stored procs, functions, views, or even table schema would fit that description. That's not a bad thing, they just give us the tools to get us started. We build what we need based off of business rules.
The split function works great for resolving situations where data was stuffed into a field instead of being normalized
don't reinvent the wheel and re-write your own split function; just take one of the fine functions off of SSC and add a nice descriptive header in it prior to putting it on your server; that way if people view the source, they'll have a good understanding of it's purpose and function.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I have no idea what you are trying to say. There is no possible way that you looked at my split function that I wrote and is not publicly distributed.
Yes, the split function I wrote was very easy to write. It took me about 3 minutes. BUT there is nothing in my split function not supported by SQL 2000. Please explain what you mean by "not supported by SQL 2000".
He is already using his own split function, which is why I used a split function. Why would you suggest that he write his own split function when he has already done so?
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Thanks for you inputs.
Here is the Split Function that I wrote from one of the scripts on the site (not sure whom I need to give credit)
CREATE FUNCTION dbo.SplitToInt (@vcDelimitedString varchar(8000),
@vcDelimiter varchar(1) )
RETURNS @tblArray TABLE
(
ElementID smallint IDENTITY(1,1), --Array index
Element int --Array element contents
)
AS
BEGIN
DECLARE
@siIndex smallint,
@siStart smallint,
@siDelSize smallint
SET @siDelSize = LEN(@vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@vcDelimitedString) > 0
BEGIN
SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF @siIndex = 0
BEGIN
INSERT INTO @tblArray VALUES(CONVERT(int,@vcDelimitedString))
BREAK
END
ELSE
BEGIN
INSERT INTO @tblArray VALUES(CONVERT(int,SUBSTRING(@vcDelimitedString, 1,@siIndex - 1)))
SET @siStart = @siIndex + @siDelSize
SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
END
END
RETURN
END
But I still get a Incorrect Syntax near '.' error when I run the query by Robert Davis.
Select A.ID As TableAID, A.LT, B.ID As TableBID, B.[Desc], B.Qty
From TableA A
Inner Join TableB B On B.ID IN
(Select Element From dbo.SplitToInt(A.Nodes, ';'))
-
There is no syntax error in the code I posted. Double check to make sure you didn't type something wrong.
If you don't see anything, please post the actual code you are using.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Here is the complete script using the split function that I had put earlier.
Create Table TableA (ID varchar(2), Nodes varchar(5), LT int)
Create Table TableB (ID int, [Desc] varchar(20), Qty int)
Insert Into TableA Values ('M1', '1;2;3', 4)
Insert Into TableA Values ('M2', '4;5;6', 5)
Insert Into TableB Values (1, 'Desc of 1', 2)
Insert Into TableB Values (2, 'Desc of 2', 13)
Insert Into TableB Values (3, 'Desc of 3', 43)
Insert Into TableB Values (4, 'Desc of 4', 52)
Insert Into TableB Values (5, 'Desc of 5', 66)
Insert Into TableB Values (6, 'Desc of 6', 77)
Select A.ID As TableAID, A.LT, B.ID As TableBID, B.[Desc], B.Qty
From TableA A
Inner Join TableB B On B.ID IN
(Select Element From dbo.SplitToInt(A.Nodes, ';'))
-
I get no errors running that code.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Interesting. I still get this error
Server: Msg 170, Level 15, State 1, Line 29
Line 29: Incorrect syntax near '.'.
Could it due to some sort of settings in Query Analyzer ?
I ran this in both SQL 2000 and SQL 2005.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply