T-SQL: Inner join on a field with 2 values

I have this query on a MS SQL Server 2005:

...INNER JOIN [table1].[Id] = [table2].[Label_Id]

Label_Id is a field containing data like this:

'Test_MyId'

I have开发者_开发知识库 a custom split Function which returns from a string a table with 2 rows.

How can I compare my table1.Id with the Id in the 2nd row of the return result of the split function?

As Quassnoi and Andomar said, until explicitly ordered, the set of rows represented by a table has no order, and the database engine is free to return the table rows in any sequential order it wants to. You could either make the second row the first row by ordering the rows in some manner, or you could modify your custom split function to return only the row you're interested in.

In SQL, there is no "first row" or "secong row" unless you define them with an ORDER BY.

SELECT  *
FROM    (
        SELECT  value, ROW_NUMBER() OVER (PARTITION BY t2.id ORDER BY …) AS rn
        FROM    table2 t2
        CROSS APPLY
                my_tvf(label_id)
        ) t2
JOIN    table1 t1
ON      t1.id = t2.value
        AND t2.rn = 2

You should write the ORDER BY condition which places the records in correct order.

The best thing would be returning an additional string_no column from your TVF (in which case you wouldn't even need a ROW_NUMBER:

SELECT  *
FROM    table1 t1
JOIN    table2 t2
ON      t1.id = t2.value
CROSS APPLY
        my_tvf(t2.label_id) s
WHERE   s.string_no = 2

You could use a subquery to manipulate the result from a table-valued UDF:

INNER JOIN [table1].[Id] = (select col2 from dbo.fnSplit(table2.Label_Id))

If you meant the second row, it becomes more complicated. You'd have to find a way to tell the database what the "second row" is; by default, rows are unordered.

INNER JOIN [table1].[Id] = 
    (
    select col1 
    from   (
           select col1
           ,      row_number() over (order by SomeColumn)
           from   dbo.fnSplit([table2].[Label_Id]
           ) as SubQueryAlias
    where  rn = 2
    )

Here, I'm ordering on SomeColumn. If you have a column that identifies the string, that would be even better.

Here's something that works in sql server 2008

DECLARE @stringsInOrder TABLE
(
  WordNumber INT IDENTITY(1,1) NOT NULL,
  Value VARCHAR(max) 
)

INSERT INTO @stringsInOrder (Value)
    (SELECT value FROM [dbo].fnSplit(@StringWhichGetsSplit))

SELECT value FROM @stringsInOrder WHERE WordNumber=2