SQL: How to replace a char or a string in data returned from a SELECT or in an UPDATE?

A. Replace one char.

Lets say you have a table FRUIT in your database.

      TABLE FRUIT

      NAME                     PRICE      DESCRIPTION

      APPLE                      2.35           #The most popular fruit.
      ORANGE                 1.23           #The most juicy fruit.


1. SELECT 

Now, you want to list the fruit name and description. By querying your database with the SQL statement below, you will have the # sign in front of each description.

      SELECT NAME, DESCRIPTION FROM FRUIT;

You can use the Replace function to remove the # sign.

      SELECT NAME, Replace(DESCRIPTION, '#', '') DESCRIPTION FROM FRUIT;

In general, you can replace char x with char y by the expression Replace(<field name>, 'x', 'y').

2. UPDATE

To remove the # sign from the DESCRIPTION field in the FRUIT table.

      UPDATE FRUIT SET DESCRIPTION = Replace(DESCRIPTION, '#', '');

B. Replace a string.

To replace a string works the same as replace one char. For example, you have the following row in your FRUIT table.

      NAME                     PRICE      DESCRIPTION

      WATER MELON    0,99           igThe most welcome fruit in summer.

You want to replace the ig with two stars **. You can use the expression below to achieve the goal.

      Replace(DESCRIPTION, 'ig', '**')



                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.