Replace works but is there a better way?

I have a string that can have zero or O in it. If it's zero, I replace it with O. If O, I replace it with zero. I need both strings ... the original one and the new one.

I did this and I repeat it for O, but is there a better way of not repeating it?

DECLARE  @searchtext nvarchar(4000) 

if CHARINDEX('0',@searchtext) > 0 -- I want to do the same check for O
	begin
		SET @searchtext2 = REPLACE(@searchtext,'0','O')
	END
    ELSE
	begin
      SET  @searchtext2 =  @searchtext -- duplicate what code does
 	END

Open in new window

LVL 8
CamilliaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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.
CamilliaAuthor Commented:
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

Open in new window

Dustin SaundersDirector of OperationsCommented:
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

Open in new window

Get a highly available system for cyber protection

The Acronis SDI Appliance is a new plug-n-play solution with pre-configured Acronis Software-Defined Infrastructure software that gives service providers and enterprises ready access to a fault-tolerant system, which combines universal storage and high-performance virtualization.

ste5anSenior DeveloperCommented:
E.g.

DECLARE @searchtext NVARCHAR(MAX) = N'Test O, zero.';

SELECT REPLACE(REPLACE(@searchtext, 'O', 'zero'), 'zero', 'O');
SELECT REPLACE(REPLACE(REPLACE(@searchtext, 'zero', 'zerXzerX'), 'O', 'zero'),'zerxzerx','O');

Open in new window

Cause the problem is that the word "zero" contains an O and that you do a double substitution, which simply applied mean the second substation would be a simple inversion of the first.

p.s. posting concise and complete examples would really help.. Thus source data and desired result.
CamilliaAuthor Commented:
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

Open 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

Open 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
CamilliaAuthor Commented:
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.
Dustin SaundersDirector of OperationsCommented:
So, what is supposed to happen if a user types:

0O19?
Dustin SaundersDirector of OperationsCommented:
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)

Open in new window

CamilliaAuthor Commented:
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
CamilliaAuthor Commented:
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.
ste5anSenior DeveloperCommented:
Nope, you didn't get it. What should happen if the user enters O0?
CamilliaAuthor Commented:
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.
CamilliaAuthor Commented:
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)
CamilliaAuthor Commented:
And same for O0019

Original one, and all zeros and all ohs.
CamilliaAuthor Commented:
CamilliaAuthor Commented:
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')

Open 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

Open in new window

Dustin SaundersDirector of OperationsCommented:
You can do that with an INNER JOIN.  Example:

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 = 'O0O1'

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

SELECT o.Id
FROM @othertable o
JOIN @results r ON o.Id LIKE '%' + r.SearchText + '%'

Open in new window


INNER JOIN will only return the results that match.  So if doing multiple
DECLARE @searchtext nvarchar(4000)
DECLARE @results TABLE(SearchText NVARCHAR(4000))
DECLARE @othertable TABLE(Id NVARCHAR(4000),ProductName nvarchar(4000))

INSERT INTO @othertable
SELECT '0001','Car' UNION ALL
SELECT '0002','Truck' UNION ALL
SELECT '0003','Computer'

SET @searchtext = 'C0mputer'

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

SELECT o.Id,o.ProductName
FROM @othertable o
INNER JOIN @results r ON o.Id LIKE '%' + r.SearchText + '%'
	OR o.ProductName LIKE '%' + r.SearchText + '%'

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CamilliaAuthor Commented:
Thanks, guys. (Thanks, ste5an, for saying I didn't understand the question I was asked of. That helped me think more and dig deeper)
CamilliaAuthor Commented:
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)
CamilliaAuthor Commented:
Someone responded and the solution seems to work. Thanks again for all the help, as always.
Dustin SaundersDirector of OperationsCommented:
Ah, sorry I don't get a chance to check EE much during the weekends, but glad to hear you got a good response!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.