Problem with using a field parameter, in a user defined function

• SqlServerCampbell

  Old Hand

  Points: 384

  More actions

  July 6, 2017 at 9:42 am

  #338197

  I’m using a function defined at 

  -- https://www.codeproject.com/Tips/798671/Split-string-into-Rows-Columns-using-Delimiters 

  for a 2008 function, but can’t figure out how to make it work with varchar field parameters.  It works fine with simple strings. 

  So e.g., 

  Select * from dbo.SplitDelimitedString('20|21/06/13#15|19/06/13#15|14/06/13','#') 

  Works fine. 

  But 

  This 

  USE mydatabase ; 
SELECT dbo.SplitDelimitedString(myfield,'|') FROM mytable ; 

  Returns 

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

  I’ve tried different combinations of prefixes, and almost always get the same result. 

  I also tried something I didn't expect to work, just for the heck of it, 

  SELECT * FROM SplitDelimitedString(mytable.pdLine,'|') ; 

  , and get the error 

     Incorrect syntax near '.'. 

  How do we send SplitDelimitedString() a field name from a table? 
  , and get the error    Incorrect syntax near '.'. How do we send SplitDelimitedString() a field name from a table?

• ScottPletcher

  SSC Guru

  Points: 101103

  More actions

  July 6, 2017 at 9:55 am

  #1949756

  If you want to apply a table-valued function to a string from a table, you need to use APPLY, typically CROSS APPLY.  Something like this:
  
SELECT *
FROM dbo.data_table mytable
CROSS APPLY dbo.SplitDelimitedString(mytable.pdLine,'|') 

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• SqlServerCampbell

  Old Hand

  Points: 384

  More actions

  July 6, 2017 at 10:28 am

  #1949765

  ScottPletcher - Thursday, July 6, 2017 9:55 AM

  If you want to apply a table-valued function to a string from a table, you need to use APPLY, typically CROSS APPLY.  Something like this:
  
  SELECT *
  FROM dbo.data_table mytable
  CROSS APPLY dbo.SplitDelimitedString(mytable.pdLine,'|') 

  Thanks, Scott.  I'll look into the compatibility level next.  I tried these two queries, with the following errors:

  SELECT * FROM dbo.mytable CROSS APPLY dbo.SplitDelimitedString(mytable.pdLine,'|') ;
  Incorrect syntax near '.'.

  , and taking out the mytable qualifier with the dot,

  SELECT * FROM dbo.mytable CROSS APPLY dbo.SplitDelimitedString(pdLine,'|') ;
  "pdLine" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 6, 2017 at 11:01 am

  #1949774

  SqlServerCampbell - Thursday, July 6, 2017 10:28 AM

  ScottPletcher - Thursday, July 6, 2017 9:55 AM

  If you want to apply a table-valued function to a string from a table, you need to use APPLY, typically CROSS APPLY.  Something like this:
  
  SELECT *
  FROM dbo.data_table mytable
  CROSS APPLY dbo.SplitDelimitedString(mytable.pdLine,'|') 

  Thanks, Scott.  I'll look into the compatibility level next.  I tried these two queries, with the following errors:

  SELECT * FROM dbo.mytable CROSS APPLY dbo.SplitDelimitedString(mytable.pdLine,'|') ;
  Incorrect syntax near '.'.

  , and taking out the mytable qualifier with the dot,

  SELECT * FROM dbo.mytable CROSS APPLY dbo.SplitDelimitedString(pdLine,'|') ;
  "pdLine" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

  Have you checked the compatibility level for your database?
  
SELECT name, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  July 6, 2017 at 11:12 am

  #1949780

  SqlServerCampbell - Thursday, July 6, 2017 10:28 AM

  ScottPletcher - Thursday, July 6, 2017 9:55 AM

  If you want to apply a table-valued function to a string from a table, you need to use APPLY, typically CROSS APPLY.  Something like this:
  
  SELECT *
  FROM dbo.data_table mytable
  CROSS APPLY dbo.SplitDelimitedString(mytable.pdLine,'|') 

  Thanks, Scott.  I'll look into the compatibility level next.  I tried these two queries, with the following errors:

  SELECT * FROM dbo.mytable CROSS APPLY dbo.SplitDelimitedString(mytable.pdLine,'|') ;
  Incorrect syntax near '.'.

  , and taking out the mytable qualifier with the dot,

  SELECT * FROM dbo.mytable CROSS APPLY dbo.SplitDelimitedString(pdLine,'|') ;
  "pdLine" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

  Something is amiss here. If pdLine is a real column in dbo.mytable then the syntax you posted should be correct. That said, you don't want to use the splitter function that you posted - instead use DelimitedSplit8K. It performs much better and is generally my splitter of choice on 2008 systems and earlier. 

  Here's the code to create and test it.
  
IF OBJECT_ID('dbo.DelimitedSplit8K') IS NOT NULL DROP FUNCTION dbo.DelimitedSplit8K;
GO
CREATE FUNCTION dbo.DelimitedSplit8K
--===== Define I/O parameters
   (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 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)
      SELECT 1 UNION ALL
      SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
      ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
      SELECT s.N1,
        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
       FROM cteStart s
      )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
   Item   = SUBSTRING(@pString, l.N1, l.L1)
 FROM cteLen l;
GO

  -- sample data for testing
IF OBJECT_ID('dbo.mytable') IS NOT NULL DROP TABLE dbo.mytable;
GO

  CREATE TABLE dbo.mytable(someId int identity, pdLine varchar(1000));
INSERT dbo.mytable(pdLine)
VALUES ('20|21/06/13#15|19/06/13#15|14/06/13'), ('55|11/08/53#15|59/06/16#15|14/06/100'),
('999|21/06/13#15|19/06/13#15|ABCDEF'), ('55|11/08/53#15|22/33/16#15|88855511');


  ...And this query is exactly what you are getting an error on except I changed the splitter name.
  
SELECT *
FROM dbo.mytable
CROSS APPLY dbo.DelimitedSplit8K(pdLine,'|');


  Results
  

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• SqlServerCampbell

  Old Hand

  Points: 384

  More actions

  July 6, 2017 at 11:46 am

  #1949789

  Alan.B - Thursday, July 6, 2017 11:12 AM

  SqlServerCampbell - Thursday, July 6, 2017 10:28 AM

  ScottPletcher - Thursday, July 6, 2017 9:55 AM

  If you want to apply a table-valued function to a string from a table, you need to use APPLY, typically CROSS APPLY.  Something like this:
  
  SELECT *
  FROM dbo.data_table mytable
  CROSS APPLY dbo.SplitDelimitedString(mytable.pdLine,'|') 

  Thanks, Scott.  I'll look into the compatibility level next.  I tried these two queries, with the following errors:

  SELECT * FROM dbo.mytable CROSS APPLY dbo.SplitDelimitedString(mytable.pdLine,'|') ;
  Incorrect syntax near '.'.

  , and taking out the mytable qualifier with the dot,

  SELECT * FROM dbo.mytable CROSS APPLY dbo.SplitDelimitedString(pdLine,'|') ;
  "pdLine" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

  Something is amiss here. If pdLine is a real column in dbo.mytable then the syntax you posted should be correct. That said, you don't want to use the splitter function that you posted - instead use DelimitedSplit8K. It performs much better and is generally my splitter of choice on 2008 systems and earlier. 

  Here's the code to create and test it.
  
IF OBJECT_ID('dbo.DelimitedSplit8K') IS NOT NULL DROP FUNCTION dbo.DelimitedSplit8K;
GO
CREATE FUNCTION dbo.DelimitedSplit8K
--===== Define I/O parameters
   (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 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)
      SELECT 1 UNION ALL
      SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
      ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
      SELECT s.N1,
        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
       FROM cteStart s
      )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
   Item   = SUBSTRING(@pString, l.N1, l.L1)
 FROM cteLen l;
GO

  -- sample data for testing
IF OBJECT_ID('dbo.mytable') IS NOT NULL DROP TABLE dbo.mytable;
GO

  CREATE TABLE dbo.mytable(someId int identity, pdLine varchar(1000));
INSERT dbo.mytable(pdLine)
VALUES ('20|21/06/13#15|19/06/13#15|14/06/13'), ('55|11/08/53#15|59/06/16#15|14/06/100'),
('999|21/06/13#15|19/06/13#15|ABCDEF'), ('55|11/08/53#15|22/33/16#15|88855511');


  ...And this query is exactly what you are getting an error on except I changed the splitter name.
  
SELECT *
FROM dbo.mytable
CROSS APPLY dbo.DelimitedSplit8K(pdLine,'|');


  Results
  

  SplitDelimitedString is working now, after setting the Compatibility Level to 90.

  I'll go ahead and test your Splitter, thank you sir.