Here's another approach you can try. The procedure depends on a simple iTVF to locate the letter locations which has the advantage of being able to find multiple instances within the same string being searched.
SAMPLE DATA
IF OBJECT_ID('tempdb..#ClientName') IS NOT NULL
DROP TABLE #ClientName
IF OBJECT_ID('tempdb..#SysRef') IS NOT NULL
DROP TABLE #SysRef
CREATE TABLE #ClientName (
[ID] INT NOT NULL
,[Name] NVARCHAR(100) NULL
,PRIMARY KEY([ID]))
INSERT INTO #ClientName
SELECT 1,'Jerry' UNION ALL
SELECT 2,'Andrew' UNION ALL
SELECT 3,'Apple' UNION ALL
SELECT 4,'Bates' UNION ALL
SELECT 5,'Doppler'
CREATE TABLE #SysRef (
[ID] INT NOT NULL
,[RefNum] NVARCHAR(100) NULL
,PRIMARY KEY([ID]))
INSERT INTO #SysRef (ID,RefNum)
SELECT 1,45678 UNION ALL
SELECT 2,54376 UNION ALL
SELECT 3,30420 UNION ALL
SELECT 4,47899 UNION ALL
SELECT 5,30421
SELECT * FROM #ClientName
SELECT * FROM #SysRef
INLINE TABLE FUNCTION TO FIND CHARS IN STRING
CREATE FUNCTION [dbo].[itvfFindPos]
(
@strInput VARCHAR(8000)
,@delimiter VARCHAR(5)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b
),
E4(N) AS (SELECT 1 FROM E2 a, E2 b
),
cteTally(N) AS (SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@strInput,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
findchar (posnum,pos)
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY t.N) AS posnum
,CHARINDEX(@delimiter,@strInput,t.N) AS pos
FROM
cteTally t
WHERE
(SUBSTRING(@strInput,t.N,1) = @delimiter)
)
SELECT
posnum
,pos
FROM
findchar
WHERE
pos > 0
)
GO
SEARCH PROCEDURE
CREATE PROCEDURE dbo.FindSingleLetterInString
@SystemNum INT
,@LetterSearch CHAR(1)
,@ReturnVal INT
AS
BEGIN
SET NOCOUNT ON
SET @SystemNum = ISNULL(@SystemNum,0)
SET @LetterSearch = ISNULL(NULLIF(@LetterSearch,''),0)
SELECT DISTINCT
c.ID
,(CASE
WHEN pos IS NULL THEN 0
ELSE @ReturnVal
END) AS refID
,name
,refNum
FROM
#ClientName c
INNER JOIN
#SysRef s
ON c.ID = s.ID
OUTER APPLY
dbo.itvfFindPos(c.Name,@LetterSearch) fp
WHERE
(@SystemNum > 0 AND s.RefNum = @SystemNum)
OR @SystemNum = 0
END
GO
FINALLY...RUN SOME EXAMPLES
DECLARE @SystemNum INT
SET @SystemNum = 30420
EXEC dbo.FindSingleLetterInString @SystemNum,'p',3 --returns only names with 'p' that match the SystemNum and flags with '3'
EXEC dbo.FindSingleLetterInString NULL,'p',3 --returns ALL names with 'p' and flags with '3'
EXEC dbo.FindSingleLetterInString NULL,'r',9 --works the same with any char(1) value or output value