Common single-line string functions of Oracle Functions

A single-line string function is used to manipulate string data. Most of them have one or more parameters, and most of them return the string ascii ()

C1 is a string and returns the ascii code of the first letter of c1. Its Inverse Function is chr () select ascii ('A') big_a, ascii ('z'') big_z from empbig_a big_z65 122

Chr () [nchar_cs]

I is a number, and the function returns the decimal characters select chr (65), chr (122), chr (223) from empchr65 chr122 chr223a z B

Concat (,)

C1 and c2 are strings. The function connects c2 to the end of c1. If c1 is null, c2. if c2 is null, c1 is returned. If c1 and c2 are both null, returns null. He and the operator | the returned results are the same select concat (''slobo'', ''svoboda'') username from dualusernameslobo syoboda

Initcap ()

C1 is a string. The function returns the first letter of each word in upper case and other letters in lower case. Words are limited by spaces, control characters, and punctuation marks. Select initcap (''veni, vedi, vici'') ceasar from dualceasarveni, vedi, vici

Instr (, [, [,])

C1 and c2 are strings, and I and j are integers. The function returns the position where c2 appears for the nth occurrence of c1, and searches for the position starting from the nth occurrence of c1. If no expected character is found, 0 is returned. If I is a negative number, the search proceeds from right to left, but the position is calculated from left to right, the default values of I and j are 1. select instr (''mississippi '','' I ',) from dualinstr (''mississippi '','' I) 11 select instr (''mississippi '','' I ',-) from dualinstr (''mississippi '','' I',) 2

Limit B (, [, I [, j])

Like the instr () function, it only returns bytes. For a single byte, bytes B () is equal to instr () length ()

C1 is a string and returns the length of c1. If c1 is null, null is returned. Select length (''ipso facto'') ergo from dualergo10

Lengthb ()

Returns bytes like length. Lower ()

Returns the lowercase character of c, which is often found in the where substring.

Select lower (colorname) from itemdetail where lower (colorname) like ''% white %'' colornamewinterwhite

Lpad (, [,])

C1 and c2 are strings and I is an integer. On the left side of c1, use the c2 string to supplement the length I, which can be repeated multiple times. If I is less than the length of c1, only the c1 characters that are as long as I are returned, and the others are truncated. The default value of c2 is single space. See rpad. Select lpad (answer, 7, ''') padded, answer unpadded from question; padded unpadded yes yesno nomaybe maybe

Ltrim (,)

Remove the leftmost character from c1 so that the first character is not in c2. If there is no c2 character, c1 will not change.

Select ltrim (''mississippi '', ''mis'') from dualltrppi

Rpad (, [,])

On the right side of c1, use the c2 string to supplement the length I, which can be repeated multiple times. If I is less than the length of c1, only the c1 characters that are as long as I are returned, and the others are truncated. The default value of c2 is single space, and other values are similar to rtrim (,)

Remove the rightmost character from c1 so that the last character is not in c2. If there is no c2 character, c1 will not change. Replace (, [,])

C1, c2, and c3 are strings. The function uses c3 instead of c2 that appears in c1 and returns the result. Select replace (''uptown '', ''up'', ''down '') from dualreplacedowntown

Stbstr (, [,])

C1 is a string, where I and j are integers. Starting from the I-bit of c1, a substring with the length of j is returned. If j is empty, it is returned until the end of the string.

Select substr (''message', 1, 4) from dualsubsmess

Substrb (, [,])

It is roughly the same as substr, except that I and j are calculated in bytes. Soundex ()

Return the words similar to c1 in pronunciation: select soundex (''dawes') dawes soundex (''daws '') daws, soundex (''dawson '') from dualdawes daws dawsond200 d200 d250

Translate (,,)

Use c3 to replace select translate (''fumble '', ''u'', ''ar'') test from dualtextramble

Trim ([[] from c3)

Delete the first, last, or all of the c3 strings. Select trim (''space padded'') trim from dual trimspace padded

Upper ()

Returns the uppercase value of c1. The select name from dual where upper (name) like ''ki % ''nameking in the where substring is often returned.

(