Solved

NULL as replacer in Replace function

Posted on 2003-10-29
5
2,488 Views
Last Modified: 2008-03-17
I have created the following function:

CREATE FUNCTION ISEQUAL(@equal_a varchar(4000), @equal_b varchar(4000))
RETURNS INT
AS
BEGIN

      IF @equal_a = @equal_b
      BEGIN
            RETURN 1;
      END
      ELSE
      BEGIN
            RETURN 0;
      END
RETURN 0
END


Basically, this function just checks to see if two values are equal and returns a one or zero. What I am trying to accomplish, is this statement:

SELECT ISNULL(REPLACE(dbo.ISEQUAL(@somevalue,@anothervalue), 0, NULL), @bettervalue);

The only problem is that, no matter what REPLACE(dbo.ISEQUAL(@somevalue,@anothervalue), 0, NULL) will ALWAYS return NULL. Why is this if there are no null comparisons? All It's doing is replacing the found value with a NULL if it finds it. Also, what is a workaround for this?
0
Comment
Question by:yodasan000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 19

Expert Comment

by:Dexstar
ID: 9646833
I can't see how you are getting those results unless @bettervalue is also NULL.  But, there should be an easier way to do what you're trying to do, but I'm not exactly sure what that is.  I see that you are trying to return @bettervalue if @somevalue and @anothervalue are not equal...  But what do you want if they are?  Just the value that they are equal to?

Try this:

     SELECT CASE WHEN dbo.ISEQUAL(@somevalue, @anothervalue) = 0 THEN @somevalue
                        ELSE @bettervalue END

Hope that helps,
Dex*
0
 
LVL 19

Accepted Solution

by:
Dexstar earned 250 total points
ID: 9646837
Bah.  I botched that.

Try this:

    SELECT CASE WHEN dbo.ISEQUAL(@somevalue, @anothervalue) = 1 THEN @somevalue
                       ELSE @bettervalue END

Enjoy,
Dex*
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9646859
yodasan000:

> Basically, this function just checks to see if two values are equal and returns
> a one or zero. What I am trying to accomplish, is this statement:

> SELECT ISNULL(REPLACE(dbo.ISEQUAL(@somevalue,@anothervalue), 0, NULL), @bettervalue);

You really don't even need that function to do that.

SELECT CASE
            WHEN @somevalue = @anothervalue THEN @somevalue
            ELSE @bettervalue
      END


Dex*
0
 
LVL 32

Expert Comment

by:bhess1
ID: 9646917
The function may be broken, but so is the logic in the SQL statement.  **Null** is not a string expression, which is what REPLACE requires for a parameter, nor can it be changed to a character data type.  Who knows what the internal code does in this case -- I would think that it should issue an error, but obviously it does not.

Dexstar -- you almost have what the user's query does:

SELECT CASE
          WHEN @somevalue = @anothervalue THEN 1
          ELSE @bettervalue
     END
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9647116
bhess1:

> Who knows what the internal code does in this case -- I would think that it
> should issue an error, but obviously it does not.

Well, I think it does an implicit conversion to character data, and then does the substitution.  For example, if you do this:
     SELECT REPLACE(123,3,8)

You Get:
     128

And if you do this:
     SELECT REPLACE(123,3,'X')

You Get:
     12X

Also, I noticed that if you do REPLACE(<ANYTHING>,<ANYTHING ELSE>, NULL), you always get NULL, which explains why the asker's original query didn't work.

Glad it's working for you,
Dex*
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question