mr_nyo mr_nyo - 4 years ago 121
MySQL Question

Replacing a string with a different string on a table in sql

Good afternoon people.

I'm trying to replace a result from a query in a particular column in sql. the table is as follows:

| firstName | lastName | major | minor | credits |
| Owen | McCarthy | Math | CSCI | 0 |
| Mary | Jones | Math | CSCI | 42 |

I need to change Math to Mathematics and CSCI to Computer Science. is there any particular code where I can replace that?

the code that I used to generate this table is this:

select firstName, lastname, major, minor, credits from student where major = 'Math' and minor = 'CSCI';

any help is appreciated, thanks and have a nice superbowl sunday

Answer Source

The best way to do this is to have a lookup table with a join. That way, you can ensure that all substitutions use the same values.

Within a single query, though, you can use the case expression. However, in your query, that is not even necessary because of the where clause. Just use constants:

select firstName, lastname,
       'Mathematics' as major, 'Computer Science' as minor, credits
from student
where major = 'Math' and minor = 'CSCI'; 

Without the where clause, you would do:

select firstName, lastname,
       (case when major = 'Math' then 'Mathematics' else major end) as major,
       (case when minor = 'CSCI' then 'Computer Science' else minor end) as minor,
from student;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download