Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

NULL as replacer in Replace function

Posted on 2003-10-29
5
Medium Priority
?
2,500 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 1000 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:Brendt Hess
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

715 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