You can create your own INSTR function leveraging ngrams8k.

CREATE OR ALTER FUNCTION dbo.Instr8K
(
  @string   VARCHAR(8000),
  @search   VARCHAR(100),
  @instance INT
)
/*
Created by...

For cleaner code you can leverage fnTally. Since fnTally leverages TOP, you can exploit row goals as Steve Collins mentioned above.

CREATE OR ALTER FUNCTION dbo.getYears(@year...

I don't have a server handy at the moment but what you are looking for is in the REPORTSERVER.DBO.CATALOG

This has the RDL XML (Report Definitions) which you can query. To...

@ Alan,

I'm honestly a little bit surprised that you're justifying a sort because it has a low row count.  We know how that usually works out.

I was a little rushed...

• This reply was modified 4 years, 9 months ago by Alan Burstein.

apart from the performance that is likely to be quite bad has errors on its construct

The Construct is a problem but I suspect the performance should be fine, STRING_SPLIT is...

there is currently no way to incrementally populate the table.

...  a [Huge] fact table that gets regenerated nightly

This is a design/process issue more than a technical one. I'll put my...

A couple things to consider. First, In SSMS 2019 the actual execution plan will show you how much time each portion of the query took. You don't need that here...

• This reply was modified 4 years, 10 months ago by Alan Burstein. Reason: Added another option - dbo.many

Grab a copy of PatReplace8K and it should be easy to strip out what you need.

SELECT 
  pa.*, 
  Clean1 = CAST(ISNULL(pr.newString,0) AS FLOAT),
...

@sgmunson

What you posted is similar to my first version of this except that I use my ngrams function to generate the tally table. Here's a simplified version of...

I missed this.

The difference is that I did the split using an indexed view. This way the split only happens once,...

Just as a side note, depending on how often your columns are updated/modified, you can always split the string ahead of time using a permanent tally table like...

Just as a side note, depending on how often your columns are updated/modified, you can always split the string ahead of time using a permanent tally table like this:

This reply was modified 5 years, 2 months ago by Alan Burstein. Reason: fixed the word "don't" so the DML is not red

This reply was modified 5 years, 2 months ago by Alan Burstein. Reason: Added Execution plans, additional notes