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...

Yet another handy use of a tally table.

https://www.sqlservercentral.com/articles/the-numbers-or-tally-table-what-it-is-and-how-it-replaces-a-loop-1

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]');

select 
 ...

• This reply was modified 5 years, 4 months ago by Steve Collins. Reason: Exclude rows without brackets

I've decided to add my name to the list of Steve's around here.  My first post on SSC was about 1 year ago.  When I signed up I...

I've decided to add my name to the list of Steve's around here.  My first post on SSC was about 1 year ago.  When I signed up I wasn't sure...

Oops wrong thread

No... you recommended using 2 queries.  Not two procedures.  If you go further up, you'll see that...

Always good to hear differing methods. Optional parameters are the bane of my life, but that's a reasonable well established method of dealing with it, and fairly low cost...

Why questions are tricky!