PL/SQL single-row functions and group functions (1)

Single-line string 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 strings.

　　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 EMP Big_a big_z 65 122

　　CHR (<I>) [nchar_cs]
I is a number. The function returns the characters in decimal format.

Select CHR (65), CHR (122), CHR (223) from EMP Chr65 chr122 chr223 A 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 operator | the returned result is the same

Select Concat ('slobo', 'svoboda') username from dual Username Slobo 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 dual Ceasar Veni, vedi, vici

　　Instr ( , [, <I> [, ])
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 ', 3, 3) from dual Instr ('Mississippi ',' I ', 3, 3) 11 Select instr ('Mississippi ',' I ',-2, 3) from dual Instr ('Mississippi ',' I ', 3, 3) 2

　　Limit B ( , [, I [, J])
Like the instr () function, it only returns bytes. For a single byte, bytes B () equals 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 dual Ergo 10

　　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 %' Colorname Winterwhite

　　Lpad ( , <I> [, ])
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 No Maybe 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 dual LTr PPI

　　Rpad ( , <I> [, ])
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 a single space. Others are similar to lpad.

　　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', 'low') from dual Replace Downtown

　　Stbstr ( , <I> [, ])
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 dual Subs Mess

　　Substrb ( , <I> [, ])
It is roughly the same as substr, except that I and J are calculated in bytes.

　　Soundex ( )
Returns a word with the same pronunciation as C1.

Select soundex ('dawes') Dawes soundex ('daws') daws, soundex ('dawson') from dual Dawes Daws Dawson D200 d200 d250

　　Translate ( , , )
Replace the characters in C1 with those in C2 with C3.

Select translate ('fumble ', 'U', 'ar') test from dual Text Ramble

　　Trim ([[ ] ] From C3)
Delete the first, last, or all of the C3 strings.

Select trim ('space padded') trim from dual Trim Space padded

　　Upper ( )
Returns the uppercase value of C1, which is often in the where substring.

Select name from dual where upper (name) Like 'Ki %' Name King