I have a pretty basic query that looks like this. It splits a cell-based on carriage return & collects the first 6 characters of a row if those characters are integers.
WITH one_note AS ( SELECT CAST( [Note] AS NVARCHAR(MAX)) AS [Note] FROM [DB].[dbo].[bvc_Notes] bn WHERE 1=1 AND [id] = 280 ) SELECT LEFT( [Item], 6 ) AS [AdjustmentID] FROM [DB].[dbo].[DelimitedSplit8K]( REPLACE(REPLACE((SELECT TOP 1 [Note] FROM one_note), CHAR(13), ''), CHAR(10), ',') ,',') asl WHERE 1=1 AND TRY_CAST( LEFT( [Item], 6 ) AS INT ) IS NOT NULL
It relies on the DelimitedSplit8K from here https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-“csv-splitter”-function
The data that it is splitting in the one field looks like this: https://postimg.cc/vDzCwg1h
The problem is that - at just 500 rows - it takes ~3 minutes to receive the query result. I get that this is unstructured data & that it can never be as efficient as structured data, but can it be made faster than it currently is? I'm using MSSQL 2012 & do not have access to newer stringsplit functions