How to replace sql field value

Question

I really don t know exactly what to do  I m a bit new to SQL    should I use substring or replace in my case  I have in my DB filed called email  now I wanted to edited all email ending with email com to email org  What shall I do

User · Answer

You could just use REPLACE    UPDATE myTable SET emailCol   REPLACE emailCol    com     org       But take into account an email address such as john comrad some company com will be updated to john orgrad some organy org    If you want to be on a safer side  you should check for the last 4 characters using RIGHT  and append  org to the SUBSTRING manually instead  Notice the usage of UPPER to make the search for the  com ending case insensitive   UPDATE myTable  SET emailCol   SUBSTRING emailCol  1  LEN emailCol -4      org  WHERE UPPER RIGHT emailCol 4       COM     See it working in this SQLFiddle

User · Answer

To avoid update names that contain  com like john comrad email com to john orgrad email org  you can do this   UPDATE Yourtable SET Email   LEFT  Email  LEN  Email  - 4    REPLACE RIGHT  Email  4     com     org

User · Answer

Try this query it ll change the records ends with  com   UPDATE tablename SET email   replace email    com     org   WHERE  email LIKE    com

User · Answer

It depends on what you need to do  You can use replace since you want to replace the value   select replace email    com     org   from yourtable   Then to UPDATE your table with the new ending  then you would use   update yourtable set email   replace email    com     org     You can also expand on this by checking the last 4 characters of the email value   update yourtable set email   replace email    com     org   where right email  4      com    However  the issue with replace   is that  com can be will in other locations in the email not just the last one  So you might want to use substring   the following way   update yourtable set email   substring email  1  len email  -4    org  where right email  4      com     See SQL Fiddle with Demo  Using substring   will return the start of the email value  without the final  com and then you concatenate the  org to the end   This prevents the replacement of  com elsewhere in the string    Alternatively you could use stuff    which allows you to do both deleting and inserting at the same time   update yourtable set email   stuff email  len email  - 3  4    org   where right email  4      com     This will delete 4 characters at the position of the third character before the last one  which is the starting position of the final  com  and insert  org instead   See SQL Fiddle with Demo for this method as well

[sql] How to replace sql field value

Examples related to sql

Examples related to sql-server-2008