Oracle Learning Fourth Chapter single line function--02__ static function

4.3 single-line character function

Common Single-line character functions include the following:

█lower

█upper

█initcap

█concat

█substr

█length

█instr

█trin

█replac

Below, we introduce them separately by example.

LOWER(column name | expression): This function is to convert a character to a new lowercase.

You can validate this single line of character functions by using the query statement in Example 4-1.

Case 4-1

sql> SELECT LOWER (' sql:structural Query Language ')
  2 from  dual;

Example 4-1 results

Here the dual makes the system a virtual table (pseudo table). So why does Oracle want to introduce this virtual table?

In our first Zhang Zhong, we must include the words in the query statement. Remember what two clauses are. The select and from two words must be included in the query statement.

But lower (' sql:structural Query Language ') does not belong to any table, how can we show it without violating the syntax of SQL? The virtual table dual provided by Oracle is used to solve this problem.

UPPER(column name | expression): This function converts characters to uppercase.

You can validate this single line of character functions by using the query statement in Example 4-2.

Case 4-2

sql> SELECT UPPER (' SQL is used exclusively in RDBMSes ')
  2 from  dual;

Example 4-2 results

initcap(column name | expression): The function is to convert the first character of each word to uppercase, and to convert to lowercase.

This single line of character functions can be validated through a query statement such as example 4-3.

Case 4-3

sql> SELECT initcap (' SQL is en 中文版 like language ')
  2 from  dual;

Example 4-3 results

CONCAT(column name | expression, column name | expression): The function is to concatenate the same string and the second string into a string.

You can validate this single line of character functions by using the query statement in Example 4-4.

Case 4-4

sql> SELECT CONCAT (' SQL allows to manipulate of the data in DB ',
  2                ' without any programming knowledge ')
  3< C4/>from dual;

Example 4-4 results

SUBSTR(column name | expression): This function returns the specified substring. The substring starts with the first m character and its length is n.

You can validate this single line of character functions by using the query statement in Example 4-5.

Case 4-5

sql> SELECT SUBSTR (' SQL lets you concentrate in what has to being done ', "
  2 from  dual;

Example 4-5 results

In this example, we omit N, and the result is to return all characters starting from the 14th character until the end.

Length (column name | expression): This function returns the length of a string in a column or in an expression.

This one-line string function can be validated by using the query statement in Example 4-6.

Case 4-6

sql> SELECT LENGTH (' SQL does not let's you concentrate ' to how it'll be achieved ')
  2 from  dual;

Example 4-6 results

INSTR(column name | expression, ' string ', [M],[n]): This function returns the numeric position of the given string, representing the start of the search from the first m character, n indicates the number of times the given string occurred, and their default value is 1.

You can verify this single-line character function by using the query statement in Example 4-7.

Case 4-7

sql> SELECT INSTR (' SQL allows for dynamic DB changes ', ' F ')
  2 from  dual;

Example 4-7 results

The above example shows that the string given is a case of fish. Therefore, we can get the result of example 4-8 by changing the ' f ' in example 4-7 to ' f '.

Case 4-8

sql> SELECT INSTR (' SQL allows for dynamic DB changes ', ' F ')
  2 from  dual;

Example 4-8 results

If the reader has time, carefully read the string in example 4-1 through example 4-8. They are actually a description of SQL.

To help the reader understand, here are the following Chinese translations:

█ example 4-1: SQL Structured Query Language

█ example 4-2: SQL only applies to relational database management systems

█ example 4-3: SQL is a language similar to English

█ example 4-4: SQL allows you to manipulate data in a database without any programming knowledge

█ example 4-5: SQL lets you focus on what you have to do

█ example 4-6: SQL does not allow you to focus your experience on how you are going to do this.

█ example 4-7: SQL allows dynamic database changes

█ example 4-8: SQL allows dynamic database changes

After you have read the above description of SQL, I believe your understanding of SQL will be more profound, perhaps to reach a new level.

TRIM([Leading|trailing|borh] to remove the character from source string): The function can be from the header (leading) part of the source string, or the tail (trailing) part, Or remove the "character to be removed" from the head (leading) and the tail (trailing) section (both).

If you do not specify a header (leading) or a tail (trailing), the trim function is handled by default (both), which is introduced in the 8i version, and two functions LTrim and RTrim in the version prior to 8i.

You can use the query in example 4-9 to remove the sql*plus before the text number.

Case 4-9

sql> SELECT TRIM ('? ') From '? Sql*plus is the SQL implementation
  2               used the Oracle RDBMS or Ordbms. ')
  3 from  dual;

Example 4-9 results

You can also remove the two text numbers after the row by using the query statement in Example 4-10.

Case 4-10

sql> SELECT TRIM ('? ') From "It can process data in sets of rows??"
  2 from  dual;

Example 4-10 results

None of the above two examples indicate whether to remove the head or tail character, so Oracle is treated by default both. But in example 4-9 only in the head there is ". "And in example 4-10 there is only at the tail." , so the Oracle system can handle it properly. But in example 4-11, this method of the trim function is going to be in trouble.

Case 4-11

sql> SELECT TRIM (' s ' sql*plus ' a fourth generation Query language ') 2 from  dual;

Example 4-11 results

In the example 4-11 SQL statement, we wanted to remove the tail's ' s ', but the Oracle system removed both the beginning and the end of the ' s '. This is obviously not the result we want. At this point we can use the trailing option to solve this problem. Now you can modify the SQL statement for example 4-11 as example 4-12

Case 4-12

Sql> SELECT TRIM (trailing ' from ' sql*plus be a foutrh generation Uery languages ') 2 from  dual;

Example 4-12 results

This time I finally got the result that I wanted.

We did not give an example of using the leading option directly in the TRIM function. If the reader is interested, try to construct one or more of these examples yourself.

replace (body expression, string to search for, replacement string): The function is to find "string to search" in "body expression" and replace it with "substitute string" if found.

This single line of character functions can be validated by the query statement in 4-13.

Case 4-13

sql> SELECT REPLACE (' sql*plus supports loops or if statements ', ' supports ',
  2                     ' does not support ')
  3  from dual;

Example 4-13 results

If the reader has time, read the strings from examples 4-9 through 4-13 carefully. They're actually a description of the Sql*plus.

To help the reader understand, here are the following Chinese translations:

█ Example 4-9: Sql*plus is an implementation of SQL, which is used in Oracle relational database management system or object-oriented relational database management system

█ Example 4-10: It can process multiple rows of data at one time

█ Example 4-11:sql*plus is a fourth generation query language

█ Example 4-12:sql*plus is a fourth generation query language

█ Example 4-13:sql*plus does not support looping and judging (branching) statements

After reading Ethan's note about Sql*plus, I believe you will not only have a deeper understanding of sql*plus, but also that the relationship between SQL and Sql*plus will be clearer. Perhaps it will come to a higher level.

-----------------------------to Be Continued------------------------

Previous: Oracle learning fourth Chapter One-line function--01

Next article: