Home SQL Server Split String function with inline sql error: The multi-part identifier "xyz" could not be bound

Questions

SQL Server Split String function with inline sql error: The multi-part identifier "xyz" could not be bound

December 5, 2022

I have a simple splitstring function that works fine when I pass a string to be split, but when I pass a value from a query it gives me the error: The multi-part identifier "xyz" could not be bound.

Here is the sample code with output.

create table test_a( col_a varchar( 10 ), col_b varchar( 10 ), col_c varchar( 10 ) )
GO
insert into test_a values( 'a', 'b', 'abc,def' )
insert into test_a values( 'c', 'd', 'ghi,jkl' )
insert into test_a values( 'e', 'f', 'mno,pqr' )

select a.col_a, a.col_b, s.item
  from test_a a, dbo.SplitString( 'abc,xyz', N',' ) s
 order by a.col_a, s.item

select a.col_a, a.col_b, s.item
  from test_a a, dbo.SplitString( a.col_c, N',' ) s
 order by a.col_a, s.item

With the first select statement I get the correct output:

col_a   col_b   item
a   b   abc
a   b   xyz
c   d   abc
c   d   xyz
e   f   abc
e   f   xyz

When I run the second select, using the value from the table, I get the error:

Msg 4104, Level 16, State 1, Line 70
The multi-part identifier "a.col_c" could not be bound.

How can I join this splitstring function to create a separate record for each string in the test_a table?

>Solution :

It looks like the problem is that you are trying to use a column from the test_a table as the input to the SplitString function, but you are not referencing the table properly in the query.

To fix the error, you need to include the test_a table in the FROM clause of the query and then use the CROSS APPLY operator to join the SplitString function to the test_a table. This will allow you to reference columns from the test_a table in the SplitString function.

Here is an example of how you could modify your query to fix the error:

SELECT a.col_a, a.col_b, s.item
FROM test_a AS a
CROSS APPLY dbo.SplitString(a.col_c, N',') AS s
ORDER BY a.col_a, s.item

The CROSS APPLY operator allows you to "join" the SplitString function to the test_a table so that you can reference columns from the test_a table in the SplitString function. This should fix the error and allow you to get the expected output.