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, 2 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!  

Ha, my recommendation is 2 procs so there's nothing to confuse.  If the OP wants to keep dealing with avoidable...

Yup, of course NULL does not equal NULL, but the fldinfo cannot be NULL (confirmed by OP).

So presumably @Parameter is optional and can be NULL. In which case the...

It's not the same query as the one the OP posted.  NULL does not equal NULL

Not quite the same as what the OP posted.  Here's another way but readability is not...

Imo it's easier to express as 2 queries

drop table if exists test_tblinfo;
go
create table test_tblinfo(fldinfo   int); 
go

insert test_tblinfo values (null), (1);

declare
  @parameter   ...