How are the CASE WHEN and the REPLACE functions different?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2012

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:

SQL

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:

SQL

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?

SQL

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

Posted 9-Aug-18 0:48am

Skipton Corp

Updated 9-Aug-18 2:15am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-08-09T01:08:00

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.

Zalak Artist · Answer 2 · 2018-08-09T02:15:00

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.

How are the CASE WHEN and the REPLACE functions different?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0