Solved

NULL as replacer in Replace function

Posted on 2003-10-29
5
2,485 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

809 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