I would start by finding all of the objects that reference the lookup table.

SELECT
  SchemaName = OBJECT_SCHEMA_NAME(sm.object_id)
, ObjectName = OBJECT_NAME(sm.object_id)
FROM sys.sql_modules...

All the outputs of your case statement need to have the same (or implicitly convertable) data type.
Also, your case statement needs to be terminated with and end

DECLARE @String VARCHAR(50) = '#BAVDB#(RowCount=3204)';

-- As a single statement
SELECT NumVal = SUBSTRING(@String, CHARINDEX('=', @String) +1, CHARINDEX(')', @String, CHARINDEX('=', @String) +1) - CHARINDEX('=', @String) -1);

--...

You can try this

select TOP 1 count(*) as [number of occurences]
from (
select case
  when answer_count between 0 and 5 then '...

Formatted SQL code that is wider than the display box does not wrap.  There are scroll bars, but they only seem to work if you highlight text and keep going....

I am really not liking the new interface.
There is waaaay too much white space.  Makes everything look clunky.

WITH cteSports AS (

SELECT * ,sn = ROW_NUMBER() OVER (PARTITION BY name ORDER BY sport)

FROM #example

)

SELECT

cte.name

, cte.city

, cte.entryDate

, sport1 = MAX(CASE WHEN cte.sn =...