Oralce Handling String Functions

Original: Oralce Handling String functions

Normally we use Oracle to have two main types of strings
1.char is always a fixed length, and if you set a value that is less than the Char column, Oracle automatically fills it with spaces. When comparing char, Oracle fills it with a space as equal length and then compares it.
The 2.VARCHAR2 data type is variable length, although it is synonymous with the varchar data type, but may change in future versions of Oracle, so you should avoid using varchar, preferably using VARCHAR2.
Fixed-length string fields use char, and all other string fields should use VARCHAR2.

Some of the functions of Oracle for string manipulation are listed below, and the following functions can be used on strings or on column names:
1.LOWER (String)Convert the input string to lowercase
2.UPPER (String)Converts the input string to uppercase
3.initcap (String)Converts the first letter of the input string word to uppercase. PS: If it is not two letters together, it is considered a new word, example: A_b A, a b similar to the previous cases, both A and B will be converted to uppercase
4.Connector (| |),Put two strings in | | Connected, you can also use the CONCAT function to concatenate strings. Example: Select CONCAT (city,country) from Table
5.Lpad and Rpad, the fill function, which is two very similar functions, is used exactly the same way. Allows a set of characters to be populated on the left [right] side of a column. Example: Rpad (city,20, '. '), if the value of ciy is not less than the length of 20, then use '. ' On the right, fill up to 20, if it is filled with a space, it looks like left-aligned.
6.Ltrim,rtrim and Trim, their role is to remove the unwanted characters from the left side of the string, the right side, and the left and then, by default deleting the space.
Format: RTRIM, LTRIM (string, ' delete character set ') the first parameter is the character to be deleted, the second parameter is a character set, and if you fill in ' AB ', Oracle will think of A and B, and then repeatedly check the right end of the string, Until each character in the string is deleted, that is, the deletion is stopped until the first character that does not appear in the deleted character set is encountered. This may be a bit less clear, see the example below
Original data
1 AAA.
2 AAA. "
3 AA. AAB
RTRIM (name, '. B "')
1 AAA
2 AAA
3 AA. Aa
If you feel that you are not clear enough, try a few more times to understand! I don't think it's as clear as it seems!
Trim format is a bit different, just look at the example bar Select Trim (' A ' from column name) from TableName or trim (leading ' a ' from column name) or trim (trailing ' a ' from column name) is equivalent to Ltri M is the same as the RTrim function, in fact the trim function is just to simplify the code.
7.LENGTHThe function is simple, which is to tell the user how long a string is, that is, how many characters are in the string, and that the function itself is not very useful, but can be used as part of other functions to calculate how many spaces a table requires or as part of an ORDER BY clause. PS: You cannot use a function such as length for a column that uses a Long data type.
8.SUBSTR (String,start [, Count])This function tells Oracle to extract a subset of string, start position, count fetch length, and not specify count, which is extracted to the end of the string by default.
Start can also be specified as a negative number, which is specified as a positive number from the beginning of the string (left), and when a negative number is specified, it is calculated from the end position (to the right). PS: Negative numbers cannot be used with char data types, because char is fixed-length, so they are populated with spaces until they extend to the full length of the column.
9.INSTRReturns the location where the specified string is located
INSTR (String,set[,start [, occurrence]]) If you specify start,oracle to skip all previous strings to that position to start the search, occurence, is forcing INSTR to skip the previous couple of matches to the string, Given the next matching position, if occurence specifies 3, that is the position of the third time. Example InStr (' Abacaaa ', ' a ', 2,2) matches a string from ABACAAA, starting at 2 positions, matching the position of the 2nd time A. PS: If a set has more than one character but a few characters, InStr gives the position of the first character in the character set.
10.CHR and ASCIIFunctions are seldom used in ad hoc queries. CHR converts a value into an equivalent ASCII string example: Select CHR from dual
The ASCII function executes exactly the opposite, passing it to the string, which is converted to the corresponding numeric value (only the first string is converted), and if you need to convert multiple strings at the same time, you can use the Dump functionDUMP(string)

The functions of Ps:instr,replace and substr have been extended to support regular expressions.

These functions can be used alone or in combination, which is a simple process that combines simple logical steps to accomplish very complex tasks.

Oralce Handling String Functions