Solved

NULL as replacer in Replace function

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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
unable to reinstall SCVMM 2012 R2 - SQL errors. 5 34
SQL Server Trigger 8 33
Open A Form without Loading All the Records of Its Recordsource 6 42
sql query 5 38
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

680 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