Result of these 2 SQLs

This is a related question that Dustin and Ste5an helped me with
https://www.experts-exchange.com/questions/29129110/Replace-works-but-is-there-a-better-way.html?headerLink=workspace_answered_questions

I'll go step by step

1. Please run the attached script. It creates the sample data

2. This works. 1265 rows. However, you see I have "@seachtext" and "@searchtext2".

DECLARE @searchtext NVARCHAR(4000) = '%019%'

declare @searchtext2 nvarchar(4000) = '%O19%'

DECLARE @results TABLE(SearchText NVARCHAR(4000))

INSERT INTO @results
	SELECT @searchtext UNION ALL
	SELECT REPLACE(@searchtext,'0','O') UNION ALL
	SELECT REPLACE(@searchtext,'O','0')

	 CREATE TABLE #unitsearch
 (
   rowid INT PRIMARY KEY
 )

 INSERT INTO #unitsearch
(
    rowid
)
SELECT --TOP 10 
 rowid

FROM

[dbo].[camillaforEE] AS u
	-- INNER JOIN @results r ON u.SerialNo LIKE @searchtext
where

		  (u.[SerialNo] LIKE @searchtext)
		  OR
           (u.[SerialNo] LIKE @searchtext2)
        
          OR
		   (u.[CustomerRef] LIKE @searchtext )
          OR (u.[Model] LIKE @searchtext )
          OR (u.[Manufacturer] LIKE @searchtext)
          OR (u.[SiteAddress] LIKE @searchtext )

DROP TABLE #unitsearch

Select allOpen in new window

3. My question above helped with replacing zero with O and vice versa. So, I removed @searchText2 and use the @result table to join. But, now I get an error that there's a duplicate for row 1518. I don't want to use "distinct" or remove "primary key". I think this isn't the right solution.

Any ideas why this doesn't work?

DECLARE @searchtext NVARCHAR(4000) = '%019%'

--declare @searchtext2 nvarchar(4000) = '%O19%'

DECLARE @results TABLE(SearchText NVARCHAR(4000))

INSERT INTO @results
	SELECT @searchtext UNION ALL
	SELECT REPLACE(@searchtext,'0','O') UNION ALL
	SELECT REPLACE(@searchtext,'O','0')

	 CREATE TABLE #unitsearch
 (
   rowid INT PRIMARY KEY
 )

 INSERT INTO #unitsearch
(
    rowid
)
SELECT --TOP 10 
 rowid

FROM

[dbo].[camillaforEE] AS u
	 INNER JOIN @results r ON u.SerialNo LIKE @searchtext
where

		  --(u.[SerialNo] LIKE @searchtext)
		  --OR
    --       (u.[SerialNo] LIKE @searchtext2)
        
    --      OR
		   (u.[CustomerRef] LIKE @searchtext )
          OR (u.[Model] LIKE @searchtext )
          OR (u.[Manufacturer] LIKE @searchtext)
          OR (u.[SiteAddress] LIKE @searchtext )

DROP TABLE #unitsearch

Select allOpen in new window

Edit  I commented the insert. I get 3 rows of 1518 but this is wrong. I should get 1265 rows....just like the first SQL I've posted above.

-- INSERT INTO #unitsearch
--(
--    rowid
--)

Select allOpen in new window

ForEE.txt