Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

NULL as replacer in Replace function

Posted on 2003-10-29
5
Medium Priority
?
2,508 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 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 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