"Incorrect syntax" using a table-valued function in SELECT clause of T-SQL query

Question

I have the following table-valued function for splitting strings. This works fine, was cribbed from elsewhere on the web, and I would have thought isn't the cause of the problem but is included in case it's relevant:

CREATE FUNCTION dbo.StringSplit (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
  WITH Pieces(pn, start, stop) AS (
    SELECT 1, 1, CHARINDEX(@sep, @s)
    UNION ALL
    SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
    WHERE stop > 0
  )
  SELECT pn,
    SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
  FROM Pieces
)
GO

This enables the following line:

SELECT * FROM dbo.StringSplit('.', 'this.is.a.string.to.split')

to produce

 pn | s
----+--------
 1  | this
 2  | is
 3  | a
 4  | string
 5  | to
 6  | split

My aim in this is to obtain a list of file extensions from a table which contains a large number of filenames. To that end, I used the above table-valued function to split each filename:

SELECT
  doc_id,
  doc_file_name,
  (SELECT TOP 1 s FROM dbo.StringSplit('.', doc_file_name) ORDER BY pn DESC) AS extension
FROM
  ai_docs_core

Having SQL Server Enterprise Manager check the syntax on that gives me a syntax error on the line containing the function:

Msg 102, Level 15, State 1, Line 34
Incorrect syntax near 'doc_file_name'.

The following doesn't do what I need (obviously, because it's using a set variable), but it doesn't cause a syntax error:

DECLARE @foo VARCHAR(512) = 'my_filename.doc'
SELECT
  doc_id,
  doc_file_name,
  (SELECT TOP 1 s FROM dbo.StringSplit('.', @foo) ORDER BY pn DESC) AS extension
FROM
  ai_docs_core

So the question is: why does using a specific field name as a parameter to the function cause a syntax error, and how can I achieve what I want?

gbn · Accepted Answer · 2011-02-24 11:05:31Z

6

I would guess that the database is set to compatibility mode 80 or lower.

See sp_dbcmptlevel

This is usually the cause if it happens in a FROM clause

You can also write it like this

SELECT
  a.doc_id,
  a.doc_file_name,
  foo.extension 
FROM
  ai_docs_core a
  OUTER APPLY
  (SELECT TOP 1 s AS extension
   FROM dbo.StringSplit('.', a.doc_file_name)
   ORDER BY pn DESC
  ) foo

answered Feb 24, 2011 at 11:05

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matt Sach Over a year ago

Correct answer. I looked at this list to see if I could see what it is that changes with the compat level relevant to this situation, and I still wasn't sure: msdn.microsoft.com/en-us/library/bb510680.aspx . For others seeing this answer, note also that sp_dbcmptlevel is due to be removed - ALTER DATABASE [dbname] SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 } is now recommended.

Collectives™ on Stack Overflow

"Incorrect syntax" using a table-valued function in SELECT clause of T-SQL query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related