SELECT REPLACE('JACK and JUE','J','BL') "Changes" REPLACE lets you substitute one string for another as well as to remove character stringsĮxample: The following example replaces occurrences of J with BL: TRANSLATE provides single-character, one-to-one substitution. REPLACE provides functionality related to that provided by the TRANSLATE function. The function returns VARCHAR2 if the first argument is not a LOB and returns CLOB if the first argument is a LOB. The string returned is in the same character set as char. Both search_string and replacement_string, as well as char, can be any of the datatypes 1) CHAR, 2) VARCHAR2, 3) NCHAR, 4) NVARCHAR2, 5) CLOB, or 6) NCLOB. If search_string is null, then char is returned. If replacement_string is omitted or null, then all occurrences of search_string are removed. REPLACE returns char with every occurrence of search_string replaced with replacement_string. REPLACE is useful for searching a pattern of characters, and then changing all instances of that pattern in a single function call. Returns a string in which all occurrences of match_string in string1 are replaced by replace_string. REPLACE function REPLACE(string1, match_string, replace_string) SUBSTR SUBSTR(col1, m ) Returns a part of col1 start at character in position (m) and continue for a length of (n) TRANSLATE TRANSLATE(col1, old, new) Changes old to new in col1 UPPER UPPER(col1) Converts col1 to upper case SOUNDEX SOUNDEX(col1) Convert col1 to a phonetic version of the word allows you to compare words that sound alike but are spelled differently. RTRIM RTRIM(col1 ) See LTRIM this is the same except that it works its way from the left to right. RPAD RPAD(col1, n, c) See LPAD this is the same except that it adds onto the right side of col1. You can include multiple characters in the (old) and (new) parameters similar to TRANSLATE. REPLACE REPLACE(col1, old, new) Replace all occurrences of the old character with the new character. LTRIM LTRIM(col1 ) Remove characters listed in (set) from the left side of col1 until a character that is not in the set is found. The table below lists all the character functions available in Oracle and what they do.įunction Syntax Description CONCAT or || CONCAT(col1, col2) or Col1 || col2 Concatenates one column with another INITCAP INITCAP(col1) Capitalizes first letter of each word INSTR INSTR(col1, c) Returns the position of (c) in col1 LENGTH LENGTH(col1) Returns the length of col1 LOWER LOWER(col1) Converts all letters to lower case LPAD LPAD(col1, n, c) Add character (c) onto the left side of col1, repeating it until col1 is (n) characters long.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |