Something like this perhaps...

DECLARE
    @ColumnName sysname = 'Referral_ID',
    @Value VARCHAR(10) = 12345,
    @SQL VARCHAR(MAX) = '';

SELECT
    @SQL = CONCAT(@SQL, 'SELECT COUNT(*) FROM...

It depends... If your data is always in that format, then yes, the SUBSTRING function is the way to go.
If the number of characters between parens can vary or...

This should do the trick...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;

CREATE TABLE #TestData (
    Column1 VARCHAR(20) NOT NULL,
    Column2 DATETIME NOT...

Updated to handle ? characters in the original string...

Declare @string nvarchar(200)
SET @string= N'?????? No payment ashdk ?&';

SELECT REPLACE(REPLACE(CAST(REPLACE(@string, '?', '```') AS VARCHAR(200)), CHAR(63),...

Try this...

Declare @string nvarchar(200)
SET @string= N'?????? No payment ashdk';

SELECT REPLACE(CAST(@string AS VARCHAR(200)), CHAR(63), '');


Something along these lines...

USE tempdb;
GO

CREATE TABLE dbo.dim_Time (
    MilTime TIME(0) NOT NULL,
    DisplayTime VARCHAR(10) NOT NULL,
    CONSTRAINT pk_dimTime_MilTime PRIMARY KEY CLUSTERED (MilTime)
    );