How to Replace a Substring in Oracle SQL

Save

Oracle allows a portion of a string, called a sub-string, to be easily replaced using structured query language (SQL) functions. The "replace" function searches through a string and replaces the target string with the supplied replacement string. Oracle SQL uses the "translate" function to modify strings. With the "translate" function, the string is examined byte by byte. If a character in the string matches a character in the "fromlist," the "translate" function replaces it with the corresponding character in the "tolist."

Replace Function

  • Sign in to SQLPlus.

  • Type a "select" command using the "replace" function and press "enter":

    select string_column, replace(string_column, "co.", "company") from string_table;

    This command retrieves the "string_column" twice so that the user can see a before-and-after representation of the data. In this case, "co." is the target and "company" is the replacement.

  • Inspect the results to see the replacement. If the "string column" contained "abc co.", it should now be converted to "abc company."

Translate Function

  • Sign in to SQLPlus.

  • Type a "select" command using the "translate" function and press "enter":

    select string_column, translate(string_column, "abc", "xyz") from string_table;

    This command retrieves the "string_column" twice so that the user can see a before-and-after representation of the data. The phrase "abc" is the "fromlist", and "xyz" is the "tolist."

  • Inspect the results to see the replacement. If "string column" contained "abc co.", it is converted to "xyz zo."

Related Searches

References

  • Photo Credit string image by Alison Bowden from Fotolia.com
Promoted By Zergnet

Comments

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!