Thanks to all for the solutions!

On a quick look, sparked enough ideas for me to try and (hopefully) write a solution - don't want to be beaten by it. I...

ZZartin thanks, that works. What if it was 'at least 4 1s' (would need new test data). Ideally the number would be variable, could that be achieved.

The @r is just to get into a table what the result should be so that it can be easily read. It will play no part in any solution.

I did...

select 'After', NULL, NULL

is not required in the result.

Yes, I wasn't arguing for natural over surrogate keys, and tend to have both if possible. An ID for simplicity of joins and a unique index on other column(s). Hope...

Only enforcing uniqueness by an identity column PK is my No. 1. Simple, but see it all the time and the disastrous consequences.

Yes, order of processing can differ - rare and not something I had seen before.

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-ver15

Yellow box warning following the section Logical Processing Order of the SELECT statement:

"The preceding sequence is...

OK. I ask because I have seen it too, despite the logical ordering of processing that I thought was the last word on it.

https://www.sqlservercentral.com/forums/topic/select-processed-before-where-clause#post-3696457

Out of interest - by 'later', are you saying that the CAST/ TRY_CAST is being executed in the SELECT before the WHERE?

AND and OR treat UNKNOWN as FALSE, and the result is what I'd expect from the WHERE clause. But this is not the case with NOT and UNKNOWN. To avoid...

Yes, when you re-arrange to that - remove the NOT - the result in 3. makes sense.

I find it difficult to see it with the original: not (a = 1...

Yes. It's not about the logical order of processing but an exception to it. Specifically, that a conversion in the SELECT can be executed before the WHERE. I came across...

Can't post code at moment. The change I made to the WHERE clause returns a subset of the rows that were successfully returned previously. But errors on casting a value...

Thanks. Will use the ISNULL to eliminate the final operation. Don't really see the need to go any further as it will probably be wrapped up in a scalar-valued function.

Ended up with this, that is closer to my actual requirement. Previously used a WHILE loop and counter.

declare @List varchar(max) = '123456789'

declare @ChunkSize int = 3

declare @SepChar char(1) = ','

declare...