From the tables provided what is the expected result?

If it were only the 100th row maybe OFFSET paging.  Since it's both the aggregation of the rows as well as the 100th value you could try using ROW_NUMBER in...

Nice one Jeff.  That's a good looking query.  It makes good use of '+'  as the NULL respecting concatenation operator.  My initial thought was to reverse the string and use...

All right it sounds tedious.  To differentiate the duplicates you're looking to add 1, 2, 3, ... n hours to the datetime column, EffectiveDate.  Of course, this could create new...

Maybe 3 WHERE clause conditions could be sufficient (A and B or C):

A) the existing criteria

B) NOT College ID 06 under Municipality 25002

C) municipal_ID equals 4269005 (which would be inclusive...

Or it could be 5 or ?

In conclusion I would recommend a crosstab-type query.  Conventions of language don't always map to relational operators.  And often predictably so.  Language conventions that is.    Are you sure you're...

Just scanning the code and there are nested Try/Catch and a transaction is declared but rollback only appears in 1 of the TRY code blocks.  That's concerning imo.  Could you...

Maybe throw a general exception and write your own error message.  Or the method returns void so maybe just "return;"?

Per SQL 2022 you could try STRING_SPLIT with the optional ordinal parameter and then recombine using STRING_AGG

drop table if exists #temp
go
select * 
into #temp
from (values ('First American...

It turns out, upon further review, a non-INT as either the numerator OR the denominator will switch the implicit conversion to NUMERIC.

select sql_variant_property(1/4, 'basetype') int_int,
  ...

• This reply was modified 3 years, 2 months ago by Steve Collins.

Needs the XML trick to reaggregate the string

declare @string varchar(max)='-0.326548;51.75514;-0.32658;51.75507;-0.326261;51.755;-0.326231;51.75508;-0.326548;51.75514;';

select *
from dbo.DelimitedSplit8K_LEAD(@string, ';') ss
 cross apply (values (iif(ss.ItemNumber%2=0, ss.ItemNumber-1, ss.ItemNumber+1))) v(new_seq);

select stuff((select ' ' + ss.Item
 ...

Instead of dividing by 100 try dividing by 100.0.  With division the implicit type conversion is to INT unless the denominator is non-INT, either NUMERIC or FLOAT.  Also, imo the...

For posterity since 2012 the built-in method for converting to base64 is using XML.  This comes up every now and then on SSC.  The only post I could find tho...

Already had dbo.fnTally.  Here are the results which show 7 or 8 seconds for CHARINDEX and 33+ seconds for STRING_SPLIT.  This is my tiny Azure SQL compatibility level 150 test...