Abdul Momen Abdul Momen - 5 months ago 10
SQL Question

How to replace part of a strings (multiple) in which, each have separate condition

I have a table named Employee which has following columns and values.

ID FNAME LNAME CITY

1 Shahriar Ahmed Dhaka
2 Mamunur Rashid Natore, Rajshahi
3 Muntasir Shafiq Bogra, Rajshahi


I need to change the part of CITY 'Rajshahi' to 'Northen Rajshahi' who has FNAME = Mamunur and LNAME = Rashid

and part of CITY 'Rajshahi' to 'Southern Rajshahi' who has FNAME = Muntasir and LNAME = Shafiq

I have tried as a newbie and those are seems to be meaning less to show up here,

Could someone help me to get over this.

Answer

You can use REPLACE function as it searches for certain characters in a string and replaces them with other characters.Furtermore, you could use CASE expression to fulfill differnt condition of replacement.

UPDATE Employee
SET  CITY = (CASE  
             WHEN FNAME = 'Mamunur' AND LNAME = 'Rashid' 
                  THEN REPLACE(city, 'Rajshahi', 'Northern Rajshahi') 
             WHEN FNAME = 'Muntasir' AND LNAME = 'Shafiq' 
                  THEN REPLACE(city, 'Rajshahi', 'Southern Rajshahi')            
             ELSE CITY
         END 
         );

ONLINE DEMO HERE

For further study go on,

https://msdn.microsoft.com/en-us/library/ms186862.aspx

http://www.sqlteam.com/article/using-replace-in-an-update-statement