Aha ok yes afaik the cache is clear-able on local instances.  I work mostly on Azure SQL these days and the DBCC statements to clear cache don't apply/work (maybe yet?). ...

Ok so is the execution cache cleared of the tally function between each execution?  Or according to what timeout period or ??

... Your solution is much more cpu heavy if the statement is only rarely executed and needs to be compiled every time. I think that the design will be...

drop table if exists #date_split;
go
Create Table #date_split
(
ID             INT,
StartDate      DATE,
EndDate ...

That was the correct clarification 🙂

Imo that does not appear to be an infallible method.  Would you agree?

In 2016+ it could be done like this

drop function if exists dbo.fnCommaOnce;
go
create function dbo.fnCommaOnce(
  @comma_str           ...

• This reply was modified 5 years, 8 months ago by Steve Collins. Reason: Added within group order by

If tvf's are allowed then I'd borrow some of Phil's code

drop function if exists dbo.fnTIDprefix_Expand;
go
create function dbo.fnTIDprefix_Expand(
  @TIDprefix        ...

Nah... it's easier than that.  Create a stored procedure that does the work, give the spreadsheet user privs to execute it, and have them execute the stored procedure from...

What Frederico wrote at the ende: "note that if this query is called multiple times for location then it should be changed so that all locations are supplied (alongside their...

Phil's solution works well.  It's more flexible in that there could be more than 1 character to prefix the []'s

Ok here's a tvf which maybe makes it more maintainable and readable.  Fwiw I don't follow Brian's approach and keeping data manipulation logic in the database seems OK imo.  To...

drop table if exists dbo.test_TID;
go
create table dbo.test_TID(
  TIDPrefix     varchar(20) not null);

insert dbo.test_TID(TIDPrefix) values
('A[123xyz]'),
('x[UY345]'),
('XP');

drop function if exists dbo.fnTally;
go
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
   ...

Cl 110 is equivalent to SQL Server 2012 iirc

This link has the implementation which (according to Jeff's notes in the function) should work for SQL Server 2008 and above

https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally

String Split won't work here as you have no delimiter on your strings.

Something to add to the TALLY solutions proposed above is that they miss the cases where there...