Result of these 2 SQLs

Camillia
Camillia used Ask the Experts™
on
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

Open 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

Open 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
--)

Open in new window

ForEE.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Data Engineer
Commented:
Convert that as an EXISTS clause and it works.

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')

	--DROP TABLE #unitsearch
	 CREATE TABLE #unitsearch
 (
   rowid INT PRIMARY KEY
 )

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

FROM

[dbo].[camillaforEE] AS u
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 )
		  OR exists (Select 1 from @results r WHERE u.SerialNo LIKE searchtext)

DROP TABLE #unitsearch

Open in new window

Thanks, let me test . Going to work soon. I'll post back.
yes, worked. Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial