CODEWITHSUNDEEP
sql-serverfunctionsplit
user7804659
user7804659

Reputation:

Applying a Function to One Row in a Column

I'm trying to apply a splitting function I wrote to one specific row in a table. Later, I'd like to apply the function to the entire column but I'm having trouble with the first step.

I've basically tried every variation below.

SalesPersons is the column I want to apply the split function to.

SELECT ID,
       (SELECT ITEM
        FROM  [dbo].[Split](SalesPersons, ','))
FROM   [dbo].[Menu]
WHERE  ID = '1234'
       AND APPLICANT = 'JohnSmith'

SELECT * dbo.Split(SalesPersons, ',')
FROM   [dbo].[Menu]
WHERE  ID = '1234'
       AND APPLICANT = 'JohnSmith'

I keep getting the following result:

Cannot find either column dbo or the user-defined function or aggregate dbo.Split, or the name is ambiguous.

If anyone could provide any feedback or help, I'd appreciate it so much!

Upvotes: 2

Views: 39

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81990

I'm assuming your split function is a Table-Valued Function

Then you would need a CROSS APPLY (use OUTER APPLY to see null values)

Example

Select A.* 
      ,B.*
 From [dbo].[Menu] A
 Cross Apply dbo.Split(SalesPersons, ',') B
 Where ID = '1234' 
   and APPLICANT = 'JohnSmith'

EDIT - If you want the split values in one row

Select A.*
      ,B.*
 From  [dbo].[Menu] A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(100)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(100)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(100)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(100)')))
                From  (Select Cast('<x>' + replace((Select replace(A.SalesPersons,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
              ) B

Upvotes: 3

Related Questions