I have a SQL 2008 database with a compatibility level of 80 (i.e. SQL 2000). I have been able to use cross apply functions, but when I add a table-valued function, this will not allow me to proceed.
I have member IDs with the following format:
EBS_322002_0397204_e
I need to get the second piece of it dynamically, since it the first piece may not always been exactly 3 characters long; otherwise, I could use the substring function and call it a day.
This is my split function:
alter FUNCTION [dbo].[fnSplit] (@sep char(1),@string varchar(8000))
RETURNS TABLE
--WITH SCHEMABINDING
AS
RETURN (
WITH Pieces(pn, [start], [stop]) AS (
SELECT 1, 1, CHARINDEX(@sep, @string)
UNION ALL
SELECT pn + 1, [stop] + 1, CHARINDEX(@sep, @string, [stop] + 1)
FROM Pieces
WHERE [stop] > 0
)
SELECT pn,
SUBSTRING(@string, [start], CASE WHEN [stop] > 0 THEN [stop]-[start] ELSE LEN(@string) END) AS string
FROM Pieces
)
First attempt:
WITH Members AS (
SELECT DISTINCT
memberid
FROM MyTable
)
SELECT * FROM Members m
CROSS APPLY dbo.fnSplit('_',m.memberid) b
Produced the error:
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'm'.
Second attempt (to get around the compatibility issue):
WITH Members AS (
SELECT DISTINCT
memberid
FROM MyTable
)
SELECT *
FROM Members m
OUTER APPLY (
SELECT TOP 1 string AS GroupNum
FROM dbo.fnSplit('_', m.memberid)
ORDER BY pn DESC
) Step1
Produced the following error:
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'm'.
I have been beating my head against a wall for a few hours and I now, do not know what to do... any suggestions?
