Replace works but is there a better way?

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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
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

Open in new window

Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ste5anSenior Developer
Commented:
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.
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
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 SaundersCo-Founder and Chief Architect
Top Expert 2016

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

0O19?
Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
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

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.
ste5anSenior Developer

Commented:
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.
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

Co-Founder and Chief Architect
Top Expert 2016
Commented:
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

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)
Someone responded and the solution seems to work. Thanks again for all the help, as always.
Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

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

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