CASE WHEN和REPLACE功能有何不同？ [英] How are the CASE WHEN and the REPLACE functions different?

问题描述

您好，



在我的SQL Server 2012 Express学习冒险期间，我遇到了W3School 。



我对它的理解有限如下：

< pre lang =SQL> SELECT REPLACE（' SQL教程'，' SQL'，' 代码项目'）;

您可以替换单词，在此例如上面的SQL字样到代码项目。



但是，我会使用CASE WHEN函数，如下所示：

  CASE  
  WHEN  fieldname = '  SQL Tutorial' 
  THEN  '  Code Project Tutorial'  
  ELSE  fieldname 
  END   AS  '  fieldname' 

CASE WHEN更有意义，我想知道你什么时候应用REPLACE功能？是否可以使用REPLACE函数而不是CASE WHEN函数？



假设，可以使用以下内容但是又有什么意义呢？

  CASE  
  WHEN  fieldname = '  SQL Tutorial' 
  THEN  REPLACE（'  SQL Tutorial'，'  SQL'，' 代码项目'）
  ELSE  fieldname 
  END   AS  '  fieldname' 

我只是有点好奇。

谢谢。



我尝试了什么：



我正在学习SQL Server 2012 Express

解决方案

替换是一个 - 极其有限 - SQL的字符串操作函数，并不像你的CASE WHEN示例那样工作。

是，

 SELECT REPLACE（'SQL Tutorial'，'SQL '，'代码项目'）; 

将为您提供代码项目教程，因此

 CASE 
 WHEN fieldname ='SQL教程'
那么'代码项目教程'
 ELSE fieldname 
 END AS'fieldname'

但是...... REPLACE版本也会改变我的SQL教程到我的CodeProject教程和这是对MySQL数据库的介绍to这是对MyCode项目数据库的介绍其中CASE WHEN版本不会改变任何东西。



使用CASE WHEN的更有意义的版本只是更清晰，因为你使用固定项来提供结果 - 而且几乎所有的SQL都没有这样做，它适用于泛型而不是固定输出。

REPLACE是一个字符串函数，允许替换特定字符串中的特定字符。



CASE WHEN就像一个条件函数，它根据语句中提供的条件做出决定。



在你的情况下，可以使用下面给出的代码，因为条件强大或静态。

 CASE 
 WHEN fieldname ='SQL Tutorial'
 THEN'Code Project Tutorial'
 ELSE fieldname 
 END AS'fieldname'

但如果您正在检查是否可以使用其他解决方案fieldname包含SQL子字符串。



例如如下：

 CASE 
当CHARINDEX（' SQL'，fieldname）> 0 
 THEN REPLACE（fieldname，'SQL'，'Code Project'）
 ELSE fieldname 
 END AS'fieldname'

在上面的代码中，条件是动态的，使用替换函数更可行。

Hello,

During my SQL Server 2012 Express learning adventures, I came across the REPLACE function taught by the W3School.

My limited understanding of it as follows:

SELECT REPLACE('SQL Tutorial', 'SQL', 'Code Project');

That you can replace the word(s), in this example the above 'SQL' word to 'Code Project'.

However, I would have used the CASE WHEN function as follows:

CASE
WHEN fieldname  = 'SQL Tutorial'
THEN 'Code Project Tutorial'
ELSE fieldname
END AS 'fieldname'

The CASE WHEN makes more sense and I'm wondering when would you apply the REPLACE function? Could the REPLACE function be used instead of the CASE WHEN function?

Suppose, the following could be used but then again what would be the point of it?

CASE
WHEN fieldname = 'SQL Tutorial'
THEN REPLACE('SQL Tutorial', 'SQL', 'Code Project')
ELSE fieldname
END AS 'fieldname' 

I'm just a little curious.
Thank you.

What I have tried:

I am learning SQL Server 2012 Express

解决方案

Replace is one the - extremely limited - string manipulation functions of SQL, and doesn;t work like your CASE WHEN example.
Yes,

SELECT REPLACE('SQL Tutorial', 'SQL', 'Code Project');

will give you Code Project Tutorial, and so will

CASE
WHEN fieldname  = 'SQL Tutorial'
THEN 'Code Project Tutorial'
ELSE fieldname
END AS 'fieldname'

But ... the REPLACE version will also change "My SQL Tutorial" to "My CodeProject Tutorial" and "This is an introduction to the MySQL database" to "This is an introduction to the MyCode Project database" where the CASE WHEN version won't change anything.

Your "more sense" version using CASE WHEN is only "clearer" because you are using fixed items to provide your results - and almost all SQL does not do that, it works with generics rather than fixed output.

REPLACE is a string function that allows to replace particular characters in a particular string.

CASE WHEN is like a conditional function that takes decisions base on the conditions provided in the statement.

In your case the below given code can be used because the condition in robust or static .

CASE
WHEN fieldname  = 'SQL Tutorial'
THEN 'Code Project Tutorial'
ELSE fieldname
END AS 'fieldname'

But the other solution can be used if you are checking if the fieldname contains "SQL" sub string.

For example as follows:

CASE
WHEN CHARINDEX('SQL',fieldname)>0
THEN REPLACE(fieldname, 'SQL', 'Code Project')
ELSE fieldname
END AS 'fieldname' 

In the above code the condition is dynamic and it is more feasible to use a replace function.

这篇关于CASE WHEN和REPLACE功能有何不同？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！