Replace works but is there a better way?

To check whether the string is in the search text is not necessary, when you always replace it. It just adds a second loop over the string.

But if I understand your problem correctly, then you need to replace one string first with a magic value (e.g. zerxzerx). Otherwise you would change it back with the second substitution.

I don't understand how I change it back. I don't change it back. This is a better example

DECLARE  @searchtext nvarchar(4000)
DECLARE  @searchtext2 nvarchar(4000)

SET @searchtext = '%019%' --now what if user has %O19%

if CHARINDEX('0',@searchtext) > 0
	begin
		SET @searchtext2 = REPLACE(@searchtext,'0','O')
	END
    ELSE
	begin
      SET  @searchtext2 =  @searchtext -- duplicate what code does
 	END

	SELECT @searchtext
	SELECT @searchtext2

Select allOpen in new window

I'm also not 100% clear, but you can always select results into a table and look at that.  I'm guessing you're doing a search and want to 'filter' on whether or not users typed in a 0 or an O?

How that would work:

DECLARE @searchtext nvarchar(4000)
DECLARE @results TABLE(Original NVARCHAR(4000),OReplace NVARCHAR(4000),ZeroReplace NVARCHAR(4000))

SET @searchtext = 'If there is a zer0 or a O then replace them because we are doing SOMETHING with that result, 100 times or, Oh, 0 times.'

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

SELECT * FROM @results

Select allOpen in new window

Oh, I now understand what you guys mean.

Let me explain again.

1. There's an input on the screen. User types 019 (zero one nine). The SQL I have, replaces the zero with O. So, now I have
@searchtext = 019 and @searchtext2 = O19

 if CHARINDEX('0',@searchtext) > 0 --check for zero
	begin
		SET @searchtext2 = REPLACE(@searchtext,'0','O')
	END
    ELSE
	begin
      SET  @searchtext2 =  @searchtext -- duplicate what code does
 	END

Select allOpen in new window

2. User now types O19 (O one nine). I can repeat the same thing I have but do a check for O

 if CHARINDEX('O',@searchtext) > 0 --check for O (not zero)
	begin
		SET @searchtext2 = REPLACE(@searchtext,'O','0')
	END
    ELSE
	begin
      SET  @searchtext2 =  @searchtext -- duplicate what code does
 	END

Select allOpen in new window

I wanted to see if there's a way of simplyfing that. So, user only enters 019 OR O19 and I want 2 results @searchtext and @searctext2

I think I can use what Dustin has. No need even to check to see if string has zero or O. I can just do a replace without the check.

So, what is supposed to happen if a user types:

0O19?

So, is something like this the desired effect?  Here's a sample:

DECLARE @searchtext nvarchar(4000)
DECLARE @results TABLE(SearchText NVARCHAR(4000))
DECLARE @othertable TABLE(Id NVARCHAR(4000))

INSERT INTO @othertable
SELECT *
FROM STRING_SPLIT('0001,0002,0003,0004,0005,0006,0007,0008,0009',',')

SET @searchtext = 'OOO1'

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

SELECT * 
FROM @othertable
WHERE Id IN (SELECT SearchText FROM @results)

Select allOpen in new window

I looked at the C# code (we're replacing that logic with SQL).

It replaces O with zero and zero with O.

so, 0O9 ....  @searchtext 0O9 and @searchstring2 = O09

I think your  last post should work. Very convulted logic the C# code has. Someone coded it couple of years ago. I'll take what you have, test and I think that should work.

Nope, you didn't get it. What should happen if the user enters O0?

You're right. I was just looking at the orig cide. Let me run Profiler, grab the SQL and see how the original code handles it (it's done with entity framework). I have to see how it's done originally.

I'll post back soon.

I turned on Profiler and looked at what entity framework generates

If user enters 0O19,  it searches for these   (same for O019)
1. 0O19 (this is the original one user entered O019 if that's entered)
2. 0019 (two zeros)
3. OO19 (2 ohs)

And same for O0019

Original one, and all zeros and all ohs.

I think what Dustin has here should work https://www.experts-exchange.com/questions/29129110/Replace-works-but-is-there-a-better-way.html?anchorAnswerId=42753791#a42753791

Yes, that solution works but now how I do add it to the where clause? (Please let me know if I should open another question)

1. Now I have the table

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

Select allOpen in new window

2. I have a where clause and now I can't use t@searchtext2 parameter. How can I replace that with selecting from the table?

   where 
	  (u.[SerialNo] LIKE @searchtext) -- I need SerailNo to select from the @results table
		  --OR
    --       (u.[SerialNo] LIKE @searchtext2) -- I can't use this anymore
        
          OR (u.[CustomerRef] LIKE @searchtext ) -- this is ok
          OR (u.[Model] LIKE @searchtext ) -- this is ok
          OR (u.[Manufacturer] LIKE @searchtext) -- this is ok
          OR (u.[SiteAddress] LIKE @searchtext ) -- this is ok

Select allOpen in new window

Thanks, guys. (Thanks, ste5an, for saying I didn't understand the question I was asked of. That helped me think more and dig deeper)

Dustin,

you have time to help me with something since you have that data I posted it last week? I have a problem with the solution that I can't figure out. If you can, I'll post my question. (I'll work on and if I figure it out by the time you see this, I'll post back)

I posted it here https://www.experts-exchange.com/questions/29129724/Result-of-these-2-SQLs.html#questionAdd

Someone responded and the solution seems to work. Thanks again for all the help, as always.

Ah, sorry I don't get a chance to check EE much during the weekends, but glad to hear you got a good response!