In this part, it will be about working with text information that can be used in queries and program code in PL / SQL.

Concat (STRL, STR2) function

This function performs the concontine string strl and STR2. If one of the arguments is NULL, then it is perceived as an empty string. If both arguments are NULL, then the function returns NULL. Example:

SELECT CONCAT ("Pap \\" was a dog ") x1,
Concatctest ", null) x2,
Concat (NULL, "TEST") x3,
Concat (NULL, NULL) X4
From dual

The dog had a dog

For concatenation, Oracle strings supports a special concatentation operator "||", which works similarly to the Concat functions, for example:

SELECT CONCAT ("Pap \\" was a dog ") x1," Pop "||" was a dog "x2
From dual

The CONCENTATION OPERATOR "||", equivalent to calling the Concat function, and the "+" operator used in arithmetic operations should not be confused. In Oracle, these are different operators, but at the expense of automatic clarification of types, employed errors are possible, for example:

SELECT "5" + "3" X1
From dual

In this case, returns numeric value 8, not the text string "53". This is due to the fact that by finding the arithmetic operation "+", Oracle automatically tries to bring the arguments to the Number type.

Lower function (STR)

The Lower function converts all STR string characters into the lowercase. Example:

SELECT Lower ("Text Data") x
From dual

FunctionUpper (STR)

The Upper function converts all Str string characters in uppercase. Example:

SELECT UPPER ("Text Data") x
From dual

INITCAP (STR) function

Returns the string of STR, in which the first letters of all words are transformed into capital. The function is convenient for formatting the full name when building reports. Example:

SELECT INITCAPCIVANTS PETR SIDOROVYCH ") X
From dual

FunctionsLtrim (STR [, SET])andRTRIM (STR [, SET])

The LTRIM function deletes all characters from the beginning of the line to the first character, which is not in the set of SET characters. By default, SET consists of a single space and may not be indicated. The RTRIM feature is similar to Ltrim, but removes characters starting from the end of the line. Consider a few examples:

SELECT LTRIM ("Text Data") x1,
Ltrim ("_ # Text Data", "#_") x2,
Ltrim ("1234567890 Text Data", "1234567890") x3
From dual

Replace function (STR, SEARCH_STR, [, REPLACE_STR])

The REPLACE function searches for the SEARCH_STR sample in the STR string and each entering the entry replaces the replace_str. By default, the replace_str is equal to an empty string, so the call of the Replace function with two arguments leads to the removal of all found entries. Finding the substring is carried out with regard to the register. Example:

SELECT REPLACE ("Popa was a dog", "Dog", "Cat") x1,
Replace ("Popa was an evil dog", "angry") x2,
Replace ("Popa was a dog", "Dog", "Cat") x3
From dual

The cat had a cat	The dog had a dog	The dog had a dog

TRANSLATE FUNCTION (STR, FROM_MASK, TO_MASK)

The Translate feature analyzes the STR string and replaces all the characters in it in the FROM_MASK row to the corresponding characters from To_Mask. For correct operation, the FROM_MASK and TO_MASK row function must have the same length or the FROM_MASK string must be longer than to_mask. If FROM_MASK is longer than to_mask, and in the processing of the string string, the symbols corresponding to one of the FROM_MASK characters are detected, and at the same time they do not have conformity in to_mask, then such characters will be deleted from the string str. If you pass from_mask or to_mask, equal to NULL, then the function will return the value of NULL. Comparison is made with regard to the register.

Select Translate ("Test 12345", "E2 \\" e! ") X1,
Translate ("Test 12345", "E234", "E") x2
From dual

This function is convenient for solving a number of practical tasks related to symbols transcoding or with a search for prohibited characters. For example, it is necessary to analyze the password and find out whether it contains at least one digit. The implementation of this check with TRANSLATE has the form:

IF Translate (Passwd, "0123456789", "*") \u003d Passwd then
Add_err0R ("Error - the password must contain at least one digit!");
Return 1;
END IF;

Another example: There is a preparation of the number to its transformation in Number. It is necessary to replace the dividers of decimal signs "," and "." on the "." And remove spaces. The implementation of this operation with TRANSLATE has the form:

Select Translate ("123 455.23", ",", "..") X1,
Translate ("- 123 455.23", ".,", "..") X2
From dual

SUBSTR function (STR, M [, N])

The Substr function returns a fragment of string string STR, starting with a symbol M of n symbols. You can not specify the length - in this case, the line from the symbol M is returned and until the end of the string str. The numbering of characters comes from 1. If you specify m \u003d 0, the copying will still start from the first character. The task of the negative value M leads to the fact that the characters are counted from the end of the line, and not from the beginning. The setting of the values \u200b\u200bM exceeding the absolute value of the string length leads to the fact that the function returns NULL.

SELECT SUBSTR ("Popa was a dog", 13) x1,
Substr ("Popa was a dog", -6) x2,
Substr ("ETO test text", 5, 8) x3,
Substr ("Popa was a dog", 150) x4
From dual

		text

INSTR (STR, SEARCH_STR [, N [, M]] function)

The INSTR function returns the position of the first symbol M-RO fragment of the string string string that coincides with the search_str string. Comparison is carried out from the N-th symbol string string, when compared, the register is taken into account. The default n \u003d m \u003d 1, that is, the search is conducted from the beginning of the line and returns the position of the first found fragment. In case of unsuccessful search, the function returns 0.

SELECT INSTR ("Ya Pop was a dog", "Dog") x1,
Instr ("Ya Pop was a dog", "Cat") x2,
Instr ("This is text to demonstrate text search", "Text", 1, 2) x3,
Instr ('11111000000001 "," 1 ", 7) x4
From dual

With this features, as well as with all other Oracle, typical errors associated with the NULL value processing are often allowed. If STR \u003d NULL, then the function will return NULL, and not zero! This must be taken into account when building various conditions. For example, this fragment of the PL / SQL program just does not take into account this feature:

IF Instr (TXT_VAR,"*") = 0 Then.
...
End.If;

In this case, it would be correct to write like this:

IF NVL (INSTR (TXT_VAR, "*"), 0) \u003d 0 THEN
...
END IF;

Length (STR) and LENGTHB (STR) functions

Length (STR) Returns Str string length in characters. For an empty string and NULL values, the function returns NULL, so it is recommended to use NVL to use with this function.

SELECT LENGTH ("Popa was a dog") x1,
Length ("") x2,
Length (NULL) x3,
NVL (Length (""), 0) x4
From dual

The LengTHB function is similar to the Length function, but returns the length of the string in bytes.

ASCII (STR) function

Returns the ASCII code of the first string symbol string in the case of using the ASCII encoding and the first byte of the multibyte symbol when using multibyte characters. Example:

SELECT ASCII ("TEST") X1 From Dual

CHR (N) function

Returns a symbol in its code.

SELECT CHR (64) X1
From dual

To simplify work with lines, there are a number of built-in functions, which greatly facilitates operations such as converting strings to other types of data, search for substring in the string, determining the length of the string, etc. In this article, we will consider the most common functions for working with rows.

1) The length definition function of the Length string (string) returns the number of characters in the string, including end spaces.

SELECT LENGTH ('String') from Dual will return the value of 7.

2) Upper symbol registers conversion functions (string), Lower (string), Initcap (string). To convert the characters to the top register uses the Upper () function.

SELECT UPPER ('String') from Dual will return String.

If you need to convert string characters to the lower case, the Lower () function is used.

SELECT Lower ('String') from Dual will return String.

The initcap function converts each first character of the word to the upper register, and all other characters to the bottom provided that the Symbol Symbol between the words space.

Select Initcap ('String1 String2') from Dual will return the string String1 String2.

3) functions for circumcision of the initial and end LTRIM spaces (string), RTRIM (string), Trim (string). Accordingly, the first function cuts all the initial string gaps, the second is all end, and the third is all initial and end.

SELECT LTRIM ('STR1') from Dual will return the str1 string,
SELECT RTRIM ('STR2') from dual will return the string str2,
SELECT TRIM ('STR3') from Dual will return the str3 string.

4) The function of replacing part of the line of another line Replace (source_stroke, replaceable_stroit, replacing_stroit). For greater clarity, consider an example, in some text field the table stores the number. Moreover, the symbol-separator between the whole and fractional part In some fields ".", And for further data processing, it is necessary that it should be "," in all fields. To do this, use the REPLACE function as follows. REPLACE (Field1, '.', ',') And all characters "." The field will be replaced with the "," field.

SELECT REPLACE ('My_String', '_', '@') from dual will return the string [Email Protected]

5) Data conversion functions to other data types. To_char (number) converts a number into text. To_number converts text to the number. To_date (string, format_dates) converts a string at a specific format.

Select to_char (123) From Dual will return the line 123,
SELECT TO_NUMBER ('12345') from dual will return the number 12345,
SELECT TO_DATE ('01 .01.2010 ',' dd.mon.yyyy ') from dual will return the date 01.jan.2010.

6) The function of determining the entry of the substring in the INSTR string (source_stroke, substring, simal number). The specified function allows you to determine the character number in the original line from which the desired substring begins (if there is such). Otherwise, 0 is returned. For example, we need to define all positions in Table1 table, in the name of which the "Manager" substring is found. For this, the next operator is quite suitable.

SELECT * from Table1 WHERE INSTR (Post, 'manager', 1)\u003e 0.

That is, the SELECT statement will display only those records from the Table1 table where the desired substring "Manager" will be found. And the search will be made from the first symbol. If the search needs to be done on another position, the character number for starting the search is indicated in the third parameter.

SELECT INSTR ('Small String', 'String', 1) from Dual returns to 7,
SELECT INSTR ('Small String', 'String', 1) From Dual will return the value of 0.

7) The selection feature in the original line Substr substring (source_stroke, the number_nachable_Simvol number, the number of_simvol). Consider such an example, the address in the user table is stored as a name. settlement, Street name, house number. Moreover, we know that there are strictly 20 characters for the name of the settlement (if the name of the settlement is less than 20 characters, the rest is filled with spaces), for the name of the street 30 characters, for the house number 3 symbol. Next, we need to transfer all the addresses from our table to another and at the same time all 3 addresses must be in different fields. To highlight the address component, apply the Substr () function.

Select Substr (Table_1.Address, 1, 20) City, Substr (Table_1.Address, 21, 30) Street, Substr (Table_1.Address, 52, 3) Town from Table_1

Of course, it is necessary to use the Insert operator to transfer the data, but for understanding the work of the SUBST function, the considered example is quite suitable.
SELECTR ('My_String', 4, 3) from Dual will return the string str.

The functions discussed above can be used in the input parameters. So if we need to highlight all characters, after some defined, then the SUBST function can pass the number of the desired symbol from the Instr function. For example, if you need to transfer all the characters from the table field, which are arranged after "," then you can use such a design
Select Substr (My_String, Instr (My_String, ',', 1), Length (My_String) - Instr (My_String, ',', 1) +1) from dual.
To determine the initial symbol, we call the INSTR () function, which will return the number of the symbol of the first entry of the substring ",". Next, we determine the number of characters until the end of the line as the difference between the length of the string and the number of the first entry substring.

8) To determine the symbol code, the ASCII (string) function is used, which returns the code 1 of the string symbol. for example

SELECT ASCII (W) from Dual will return the value of 87.

9) Reverse function for converting a symbol code into the CHR symbol (number).

SELECT CHR (87) From Dual will return the W. Symbol

Functions for working with numbers in Oracle.

Oracle's DBMS has a number of functions for working with numbers. These include the rates of the number of Power (), rounding Round (), etc.

1) The ABS function returns the absolute value of the argument.
SELECT ABS (-3) from Dual will return the value of 3.

2) The Ceil (number) function returns the smallest integer, greater or equal to the transmitted parameter.
SELECT CEIL (4.5) from Dual will return value 5.

3) FLOOR (number) function returns the largest integer, smaller or equal to the transmitted parameter.
SELECT FLOOR (3.8) from dual will return the value of 3.

4) The MOD function (number_1, number_2) returns the remainder of the division of the first parameter to the second.
SELECT MOD (5, 3) from Dual will return the value 2. Note. If the second parameter is 0, then the function returns the first parameter.

5) Round rounding function (number_1, number_2). Rounds the first transmitted parameter to the number of discharges transmitted in the second parameter. If the second parameter is not specified, then it is received equal to 0, that is, the rounding is performed to the whole value. Examples
SELECT ROUND (101.34) from dual will return value 101,
SELECT ROUND (100.1268, 2) from dual will return 100.13
SELECT ROUND (1234000.3254, -2) from dual will return to 1234000,
SELECT Round (-100.122, 2) from dual will return the value -100.12.

6) Trunc value trousers (number_1, number_2). Returns the truncated value of the first parameter to the number of decimal discharges specified in the second parameter. Examples
SELECT TRUNC (150.58) from dual will return the value of 150
Select Trunc (235.4587, 2) From Dual will return to 235.45
SELECT TRUNC (101.23, -1) from dual return value 100

7) Oracle's DBMS has a number of trigonometric functions sin (number), COS (number), Tan (number) and the reverse ACOS (number), ASIN (number), ACOS (number). They return the value to the corresponding name of the trigonometric function. For direct functions, the parameter is the value of the angle in radians, and for the reverse - the value of the function. Examples
SELECT COS (0.5) from dual will return the value of 0.877582561890373
SELECT SIN (0.5) from dual will return the value of 0.479425538604203
SELECT TAN (0.5) from dual return the value of 0.546302489843791
SELECT ACOS (0.5) from Dual will return the value 1.0471975511966
SELECT ASIN (0.5) from dual will return 0.523598775598299
SELECT ATAN (0.5) from dual return the value of 0.463647609000806

8) hyperbolic functions. Sinh (number),
COSH (number), TANH (number). SINH () Returns the hyperbolic sine transmitted parameter, Cosh () returns the hyperbolic cosine of the transmitted parameter, TANH () returns the hyperbolic tangent of the transmitted parameter. Examples
SELECT COSH (0.5) from dual will return the value 1.12762596520638
SELECT SINH (0.5) from dual will return the value of 0.521095305493747 SELECT TANH (0.5) from dual return the value of 0.46211715726001

9) ENGLISH FUNCTION POWER (number_1, number_2). Examples
SELECT POWER (10, 2) from dual return value 100
SELECT POWER (100, -2) from dual will return 0.0001

10) Logarithmic functions. LN (number) Returns the natural logarithm of the transmitted parameter, log (number_1, number_2) returns the logarithm of the second transmitted parameter based on the base transmitted by the first parameter. Moreover, the first parameter must be greater than zero and is not equal to 1. Examples
SELECT LN (5) from dual will return the value 1.6094379124341
SELECT log (10, 3) from dual will return the value of 0.477121254719662

11) SQRT square root extraction function (number). Example
SELECT SQRT (4) from Dual will return the value of 2.

12) EXP (number) EXP (number) function. Example
SELECT EXP (2) From Dual will return to 7.38905609893065.

Features for working with dates in Oracle

In practice, it is often necessary to analyze data in the form of dates, to produce some operations on them, change the format. All these operations are already implemented in the form of built-in functions. Consider the most basic ones.

1) Add_Months Returns the date that is separated from the date transmitted in the first parameter to the number of months specified in the second parameter. Examples
Select Add_Months ('01 -Jan-2010 ', 2) From Dual will return the date '01 .03.2010'
Select Add_Months ('01 -Jan-2010 ', -3) from Dual return the date '01 .10.2009'
Select Add_Months ('30 -Jan-2010 ', 1) From Dual will return the date '28 .02.2010'

2) to determine current date And the time is applied SYSDATE function. The scope of this function is much wider than it may seem at first glance. First of all, it is control over the entry of data in the database. In many tables, separate fields are allocated to save the date of the last change. It is also very convenient to control some input parameters for reports, especially if they should not be more than the current date. In addition to the date, this feature returns more time up to seconds. Example
SELECT SYSDATE FROM DUAL RETTY DATE '22 .05.2010 14:51:20 '

3) If you need to define the last day of the month, the Last_Day function (date) is quite suitable for this. It can be used to determine the number of days remaining in the month.
SELECT LAST_DAY (SYSDATE) - SYSDATE FROM DUAL.
As a result of the execution of this operator, the number of days from the current date before the end of the month will be displayed. Example
SELECT Last_Day ('15 -Feb-2010 ') from Dual will return the date '28 .02.2010'.

4) Function to determine the number of months between dates of MONTHS_BETWEEN (date_1, date_2). Examples
SELECT MONTHS_BETWEEN ('01 -JUL-2009 ', '01 -JAN-2010') from dual return value -6
SELECT MONTHS_BETWEEN ('01 -JUL-2009 ', '10 -Jan 2010') from Dual will return the value -6.29032258064516.
Note. If the days of the months coincide, the function returns an integer, otherwise the result will be fractional, and the number of days in the month will be taken 31.

5) The next_day function (date, day_deli) allows you to determine the following date from the date transmitted in the first parameter, which corresponds to the day of the week transmitted in the second parameter. Example
SELECT NEXT_DAY ('01 -JUL-2009 ',' MON ') from dual will return the date '06 .07.2009', that is, the next Monday after July 1, 2009 has come 6 numbers.

6) Rounding the date of the Round (date, format). The second parameter is not required if it does not specify, then it is taken for 'DD', that is, rounding will be produced until the near future. Examples
SELECT ROUND (SYSDATE) from dual will return the date '23 .05.2010 '
SELECT ROUND (SYSDATE, MONTH) From Dual will return the date '01 .06.2010 ', rounded to the nearest day of the month.

7) truncation of the date. Trunc feature (date, format). Also, as discussed above may not have the second parameter. In this case, the truncation will be made until the near future. Examples
SELECT TRUNC (SYSDATE) from dual will return the date '22 .05.2010 '
SELECT TRUNC (SYSDATE, 'WW') FROM DUAL will return the date '01 .05.2010 '
Select Trunc (Sysdate, 'Day') from Dual will return the date '16 .05.2010 '.

Data conversion functions in Oracle

This section is devoted to considering data transformation into various formats. In practice, the situation is quite common when it is necessary to consider string values \u200b\u200bas numbers and vice versa. Despite a small amount of The functions of their capabilities are quite enough to solve very complex applied tasks.

1) to_char (data, format). At first glance, the syntax is pretty simple, but due to the second parameter, you can very accurately describe in which format to convert data. So in the string you can convert both the date and numeric value. Consider the option of converting the date to the string. The values \u200b\u200bof the most common formats are shown in the table, more complete information is contained in the technical documentation.

Table of format values \u200b\u200bfor conversion of a number into a string.

Select to_char (sysdate, 'd-month-yy') from dual will return the string '7-May -10'
SELECT TO_CHAR (SYSDATE, 'DDD-MM-YYYY') from dual will return the line '142-05-2010'
Select to_char (sysdate, 'q-d-mm-yyy') from dual will return the line '2-7-05-010'
SELECT TO_CHAR (1050, '9.99Eeeee) from dual will return the line' 1.050E + 03 '
SELECT TO_CHAR (1400, '9999V999') from dual will return the line '1400000'
Select to_char (48, 'Rm') from dual will return the line 'xlviii'

2) Row conversion function at to_date (string, format). Possible formats are already considered above, so I will give some examples of using this function. Examples
Select to_date ('0101.2010 ',' dd.mm.yyyy ') from dual will return the date '01 .01.2010'
Select to_date ('01 .jan.2010 ',' dd.mon.yyyy ') from dual return the date '01 .01.2009'
SELECT TO_DATE ('15 -01-10 ',' DD-MM-YY ') from dual return the date '15 .01.2010'.

3) Row conversion function to the numeric value to_number (string, format). The most common formats are listed in the table, so consider the use of this function on the examples. Examples
SELECT TO_NUMBER ('100') from dual will return the number 100
SELECT TO_NUMBER ('0010.01', '9999D99') from dual return number 10.01
SELECT TO_NUMBER ("500,000", "999G999") from dual will return the number 500000.

Introduction

oracle register string arithmetic

The main ideas of modern information technology are based on the concepts that data should be organized in databases to adequately display the changing real world and meet the information needs of users.

Relevance:The computer is nothing more than the information conversion device. If the information is not very much, the main time when processing it occupies the transformation algorithm itself. If at the same time it is necessary to work with large amounts of data - the processing efficiency begins to directly depend on the efficiency of obtaining data, filtering them, etc. In order to simplify the development process, enhance the performance of such systems, various DBMSs were created. They have their own format of data storage, their algorithms for their search and extract, but the basic query language from most of them is one. And this language is SQL.

Objectives:

1. Obtaining theoretical and practical knowledge of functions Oracle SQL.;

2. Application of the theory in practice.

Tasks: This course is devoted to the study of Oracle SQL functions, as well as the main techniques for working with them. In this paper, we will study the functions that work with simple single-line input parameters returning for each row.

Oracle SQL functions

The function is similar to the operator in the fact that it manipulates data elements and returns the result. Functions differ from the operators of the format in which they are specified with their arguments. This format allows functions to operate on zero, one, two or greater number of arguments:

function (Argument, Argument, ...)

Functions can be used to perform calculations with data, data types conversion, changes to the output formats, etc. SQL functions are two main types:

1. single-line (or scalar) functions;

2. Group (or aggregate) functions.

These functions differ in the number of rows on which they operate. A single-line function returns the only value for each table row, while the group function returns the only value for a whole group of rows.

Single-line functions . Single-line functions can appear in SELECT, WHERE and ORDER by SELECT commands. As an argument, they can take constants specified by the user, the values \u200b\u200bof the variables, the names of the column of the database table or expression, composed using operators and functions.

List of functions

All single-line functions are usually divided into several groups by type of these arguments and returned values. Allocate:

· Numeric functions;

· Symbolic functions;

· Features for working with dates;

· Conversion functions.

Symbolic functions

Symbolic functions are operated on string values. To simplify work with lines, there are a number of built-in features, which greatly facilitates various operations.

Symbol register conversion functions

The length definition function of the Length string (string) returns the number of characters in the string, including end spaces.

SELECT Length ( string) From. Dual. return value 7.

Functions for converting the parameters of the Upper characters (string), Lower (string), Initcap (string). To convert the characters to the top register uses the Upper () function.

SELECT Upper ( string) From. Dual.ropsp String.

If you need to convert string characters to the lower case, the Lower () function is used.

SELECT Lower ( String) From. Dual. Ropsp String.

The initcap function converts each first character of the word to the upper register, and all other characters to the bottom provided that the Symbol Symbol between the words space.

SELECT Initcap (String1 String2) From dual Return the line String1 String2..

Functions for circumcision of the initial and end LTRIM spaces (string), RTRIM (string), Trim (string). Accordingly, the first function cuts all the initial string gaps, the second is all end, and the third is all initial and end.

SELECT Ltrim (`Str1") from Dual will return the str1 string,

SELECT RTRIM (`Str2") from Dual will return the string str2,

SELECT Trim (`Str3") from Dual will return the strcon string.

The replacement function of the line part of another line Replace (source string, replaced by a substring replacing substring). For greater clarity, consider an example:

In some text field, the table stores the number, and the separator symbol between the whole and fractional part in some fields ".", And to us for further data processing it is necessary for it in all fields to be ",".

To do this, use the Replace function as follows: Replace (Field1, ".", ",") And all characters "." The field will be replaced with the "," field.

SELECT Replace (`my_string", "_", "@") from dual will return the string [Email Protected]

Data conversion functions to other data types: to_char (number) Converts a number to text, to_number (string) converts text to the number, to_date (line, date format) converts a string at a specific format.

SELECT To_char (123) from dual will return the string 123,

SELECT To_number (`12345") from dual will return the number 12345,

The function of determining the entry of the substring in the INSTR string (source string, substring, the character number). This feature allows you to define the character number in the original line from which the desired substring begins (if there is such). Otherwise, 0, for example, we need to define all positions in Table1 table, in whose name is the "Manager" substring. For this, the following operator is quite suitable:

SELECT*From.Table1 WhereInstr (Post, `Manager, 1)\u003e 0.

That is, the SELECT statement will display only those records from the Table1 table, where the desired substring "Manager" will be found. And the search will be made from the first symbol. If the search needs to be done on another position, the character number for starting the search is indicated in the third parameter.

SELECT Instr (`Small String,` String, 1) From Dual will return the value of 7,

SELECT Instr (`Small String,` String, 1) From Dual will return the value of 0.

The selection function in the original line Substr substring (source string, the initial character number, the number of characters). Consider such an example in the user table stores the address in the form of the name of the settlement, the name of the street, the house number. Moreover, we know that there are strictly 20 characters for the name of the settlement (if in the name of the settlement less than 20 characters, the rest is filled with spaces), for the name of the street 30 characters, for the house number 3 symbol. Next, we need to transfer all the addresses from our table to another, while all 3 addresses must be in different fields. To highlight the address component, apply the Substr () function.

SELECTSubstr (Table_1.Address, 1.20) City, Substr (Table_1.Address, 21,30) Street, Substr (Table_1.Address, 52, 3) Town From. Table_1;

Of course, it is necessary to use the insert operator to transfer the data, but the considered example is quite suitable for understanding the operation of the SUBST function.

SELECT Substr (`my_string, 4, 3) from dual will return the string str.

The functions discussed above can be used in the input parameters. So if we need to highlight all characters, after some defined, then the SUBST function can pass the number of the desired symbol from the Instr function. For example, if you need to transfer all the characters from the table field, which are arranged after "," then you can use such a design:

SELECT Substr (My_String, Instr (My_String, `,", 1), Length (My_String) - Instr (My_String, `,", 1) +1) from dual.

To determine the initial symbol, we call the INSTR () function, which will return the number of the symbol of the first entry of the substring ",". Next, we determine the number of characters until the end of the line as the difference between the length of the string and the number of the first entry substring.

To determine the symbol code, the ASCII (string) function is used, which returns the code 1 of the string symbol. For example:

SELECT ASCII (W) From Dual will return the value of 87.

The back function of the symbol code conversion to the CHR symbol.

SELECTChr (87) from dual will return the W. Symbol

Symbolic string manipulation functions

Oracle offers an extensive set of functions for manipulating string data:

CHR (N) - Returns the Symbol of the ASCII code for the decimal n;

ASCII (S) - returns decimal ASCII code of the first line symbol;

INSTR (S2. S1.POS [, N] - Returns the position of the string S1 in a string S2 greater or equal to POS. N - the number of occurrences;

Lenght (s) - Returns the length of the line;

Lower (s) - replaces all string characters on capital characters;

Initcap (s) - sets the first symbol of each word in the string to the title, and the remaining symbols of each word - on capital;

SUBSTR (S, POS, [, LEN]) - highlights the LEN long line in the string s, starting from the POS position;

Upper (s) - converts uppercase letters in the row to capital letters;

LPAD (S, N [, A]) - Returns the string S, supplemented on the left symbols A to the number of characters N. The default symbol is a space;

RPAD (S, N [, A]) - Returns a string S, supplemented on the right symbols A to the number of characters N. Symbol - the default filler - space;

Ltrim (s,) - Returns the truncated left line S. Symbols are deleted until the removed symbol is included in the string - the S1 template (default - space);

Rtrim (s,) - Returns a truncated row of S. Symbols are deleted until the removed symbol is included in the string - the S1 template (default - space);

Translate (S, S1, S2) - Returns the string S, in which all the entry of the S1 string is substituted with a string S2. If s1<> S2, then symbols that no conformity are excluded from the resulting string;

Replace (S, S1, [, S2]) - Returns the string S, for which all the entry of the S1 string is substituted on the S2 substitution. If S2 is not specified, then all the enters the substring S1 are removed from the resulting string;

NVL (x, y) - if x is NULL, then returns to Y or a string, or a number or a date depending on the source type Y;

Soundex (s) - Returns the phonetic representation of the line;

Laboratory work. Work with string functions

String functions in Oracle SQL queries, Upper (), Concat (), Substr ().

The task:

You needed to create an identifier for each employee who should look like 3 first character name symbols Two first surname symbols. All characters of this identifier must be presented in the upper case.

Write a request that would return from the HR.Employees table information about the name and surname of the employee, as well as an employee ID in accordance with the conditions set. The result of the query must be as presented in Fig. 3.1-1.

Decision:

The code of the corresponding request may be:

SELECT FIRST_NAME AS "Name", Last_Name AS "Surname", Upper (Concat (substr (first_name, 1, 3), substr (last_name, 1,2))) AS "Identifier" From. Hr.Employees.

A symbolic function receives one or more character values \u200b\u200bas a parameter and returns the character and numeric value. If the character function returns a character value, it always has the VARCHAR2 type (variable length) - except for Upper and Lower functions. These functions convert a predetermined line to the upper or lower register, respectively, and return the value of the fixed length of the CHAR type, if the row transmitted in the arguments had a type of char.

Brief summary of string functions

As mentioned earlier, PL / SQL provides a wide range of powerful, high-level string functions to the programmer to obtain information about lines and modifying their contents. The following list gives an idea of \u200b\u200btheir capabilities and syntax. For full information about specific functions, refer to the Oracle SQL Reference directory.

• ASCII (Symbol) Returns Numeric Code (Number) of the specified symbol in the database set.
• ASCIISTR (Line1) Gets a string in any character set and converts it to the ASCII-symbol string. All characters that are missing in the ASCII encoding are presented in the form \\ xxxx, where XXXX is a symbol code in Unicode.

For information about unicode and codes of characters, please contact the site http://unicode.org.

• CHR (code)
  Returns the VARCHAR2 type symbol (length 1) corresponding to the specified code. The function is reverse with respect to the ASCII function. She has a variety, convenient when working with data in national symbol sets:

CHR (USING NCHAR_CS code)

Returns a symbol of the NVARCHAR2 type from the national set of characters.

• Compose (line1)
  Gets a string of symbols in Unicode format and returns it in normal form. For example, an abnormalized representation "A \\ 0303" defines the symbol "A" with the Tilda upstairs (that is, a). Call compose ("A \\ 0303") Returns the value "\\ 00E3" - hexadecimal symbol A in Unicode.

In Oracle9i Release 1, the Compose function could only be called out of SQL commands; In PL / SQL programs, it could not be used. Starting with Oracle9i Release2, the Compose function can also be used in PL / SQL expressions.

• Concat (string1, line2)
  Joins the string2 to the end of the line1. A similar result can be achieved using the expression line1 || String2. Operator || Much more convenient, so the Concat function is relatively rare.
• Convert (line1, set_Simvols)
  Converts a string from a database symbol set to a specified set of characters. When calling, you can also set the source set of characters:

Convert (String1, end_naboting, source_nab)

• Decompose (line1)
  Gets a string in Unicode and returns a string in which all composite characters are decomposed on the elements. The function is reverse with respect to Compose. For example, the Decompose call returns the "A ~" string (see Compose).

There are two varieties of this feature:

• Decompose (String1 Canonical)
  Performs canonical decomposition; The result obtained can be restored by calling Compose. Used by default.
• Decompose (line1)
  Decomposition is performed in the so-called compatibility mode. Call recovery COMPOSE may be impossible.

The DECOMPOSE function, like Compose, cannot be directly called in PL / SQL expressions in Oracle9i Release 1; It must be called from SQL instructions. Starting with Oracle9i Release 2, this restriction was removed.

• Greatest (String1, line2, ...)
  Gets one or more lines and returns a string that would be the latter (that is, the greatest) when sorting input rows ascending. Also, see the description of the Least function inverse to the Greatest.
• Initcap (line1)
  Changes the register of symbols of the string argument, translating the first letter of each word of the line to the upper case, and the remaining letters in the lower. A word is considered a sequence of characters separated from the remaining symbols by a space or a symbol that is not alphanumeric (for example, # or _). For example, the Initcap call ("This Is Lower") gives the result "This Is Lower".
• Instr (string1, line2)
  Returns the position with which the line2 is included in the string1; If the entry is not detected, the function returns 0.

There are several varieties of this feature:

• INSTR (line11, line2, initial_position)
  The search for string2 in the string1 begins from the position specified by the last parameter. By default, the search begins from position 1, so that the Instr (String1, String2, 1) call is equivalent to an Instr call (String1, String2).
• Instr (string1, line22, negative_nachable_Position)
  The offset of the initial position is set not from the beginning, but from the end line1.
• Instr (string1, line22, initial_position, n)
  Find out n-E entry Strings2, starting with a given initial position.
• Instr (string1, line22, negative_nachable_Position, N)
  Finds the N-E entry of the line2, starting from the specified initial position from the end of the line1.

The INSTR function considers the string as a sequence of characters. Its varieties of Instrb, Instr2 and Instr4 consider the string as a sequence of bytes, code units (Code Units) or code indexes (Code Points) of Unicode, respectively. A variety of Instrc considers the string as a sequence of complete symbols of Unicode. For example, the string "A \\ 0303", which is a decomposed equivalent "\\ 00E3", or a, is considered as one character. On the contrary, the INSTR function considers "A \\ 0303" as a sequence of two characters.

• Least (string1, line2, ...)
  Gets one or more lines and returns a string that would be the first (that is, the smallest) when sorting input rows ascending. See also the description of the GREATEST function inverse to Least.
• Length (line1)
  Returns the number of characters in the string. Varieties of Lengthb, Length2 and Length4 return the number of bytes, code units or code indices (Code Points) of Unicode, respectively. The variety of Lengthc returns the number of unicode full symbols normalized as far as possible (that is, with the conversion "A \\ 0303" in "\\ 00E3").

The Length function usually does not return zero. Recall that Oracle considers an empty string (") as NULL, so the Length call (" ") is actually equivalent to an attempt to obtain NULL length; Its result will also be equal to NULL. The only exception is found when applying Length to the CLOB type. The CLOB type may contain 0 bytes and at the same time be different from NULL. In this single case, Length returns 0.

• Lower (String1)
  Converts all letters set string in the lower case The function is reverse to Upper. The type of return value matches the type of input data (Char, Varchar2, CLOB). Also, see NLS_LOWER.
• LPAD (line1, final_dlin)
  Returns the value of the string1, supplemented on the left spaces to the final_dlin. The function has the following variety:
• Lpad (line1, final_dlin, aggregate)
  Joins a sufficient amount of complete or partial entries in order for the total length of the string reached the resulting final_dlin. For example, a call lpad ("Merry Christmas!", 25, "Ho!") Return the result "Ho! Ho! Hmerry Christmas!".

• ? Ltrim (line1)
  Removes spaces from the left edge line1. See also Descriptions of Trim functions (ISO standard) and RTRIM. The function has the following variety:
• Ltrim (line1, removable_nab
  Removes any characters that are included in the string removed_name, from the left edge of the line1.
• NCHR (code)
  Returns the NVARCHAR2 type symbol (length 1) corresponding to the specified code. The CHR function with the condition using nchar_cs implements the same functionality as NCHR.
• Nls_initcap (string1)
  Returns the version of the string1, which should refer to the Nvarchar2 or NChar type, in which the first letter of each word is translated into the upper case, and the remaining letters in the lower. The function returns the value of the VARCHAR2 type. "Word" is considered a sequence of characters separated from the remaining symbols by a space or a symbol that is not alphanumeric.

You can set the sorting order affecting the definition of the "first letter":

• Nls_initcap (string1, "nls_sort \u003d rule_sortion")
  In this syntax form, the relative rule is one of the permissible names of the sorting rules listed in the Oracle Database Globalization Support Guide manual, Appendix A, section "Linguistic Sorts".

The following example shows what the Initcap function is different from NLS_initcap:

Begin dbms_output.put_line (initcap ("ijzer")); Dbms_output.put_line (nls_initcap ("ijzer", "nls_sort \u003d xdutch")); End; Result: ijzer ijzer

In the Dutch language, the sequence of characters "? »Considered as one character. The NLS_INITCAP function recognizes this circumstance when specifying the NLS_SORT rule and correctly converts the characters of the word "zer" ("iron" in Netherlands).

• NLS_LOWER (string1) and NLS_LOWER (line1, "nls_sort \u003d rule_sortion") Returns a string1 converted to the lower register according to the rules of the specified language. About how the NLS_SORT may affect the conversion result, is described in the description of the NLS_INITCAP function.
• Nls_upper (line1) and nls_upper (string1, "nls_sort \u003d rule_r_sortion") Returns a string1 converted to the top register according to the rules of the specified language. About how the NLS_SORT may affect the conversion result, is described in the description of the NLS_INITCAP function.
• NLSSORT (line1) and NLSSORT (line1, "NLS_SORT \u003d Sorting rule") Returns a string of bytes that can be used to sort the string value according to the rules of the specified language. Row returns to B. rAW format . For example, a comparison of two rows according to the rules of the French language is performed as follows: if nlssort (x, "nls_sort \u003d xfrench")\u003e nlssort (y, "nls_sort \u003d xfrench") then ... If the second parameter is not specified, the function uses the default sorting order appointed for session. Full list The rules are given in the Oracle Database Globalization Support Guide manual, Appendix A, section "Linguistic Sorts".
• REGEXP_COUNT, REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR for descriptions of these functions designed to work with regular expressions can be studied.
• REPLACE (line1, desired_stroke, replacement) Returns the string obtained as a result of replacing all occurrences of the desired_stroke in the string1 of the replacement string. The REPLACE function can be used to replace all occurrences of a specific substring in one instruction.
• REPLACE (line1, desired_stalk)
  Returns the string obtained as a result of the removal of all occurrences of the desired_stroke from the string1.
• RPAD (line11, final_dlin)
  Returns the value of the string1, supplemented on the right spaces to the final_dlin. The function has the following variety:
• RPAD (line1, final_dlin, aggregate)
  Joins a sufficient amount of complete or partial entries in order for the total length of the string reached the resulting final_dlin. RPAD call ("Merry Christmas!", 25, "Ho!") Return the result "Merry Christmas! Ho! Ho!".

The RPAD function complements the string on the right, and the pair of the LPAD function is on the left.

• RTRIM (line1)
  Removes spaces from the right edge of the string1. See also the descriptions of Trim functions (ISO standard) and Ltrim. The function has the following variety:
• RTRIM (string1, deleted_nabot)
  Removes any characters included in the string removed_nabor, from the right edge of the line1.
• SOUNDEX (line1)
  Returns a string with a "phonetic representation" of the argument.
  Example:

Soundex ("Smith") -\u003e "S530" SoundEX ("SMYTHE") -\u003e "" S530 "Soundex (" Smith Smith ") -\u003e" S532 "SoundEX (" Smith Z ") -\u003e" S532 "SOUNDEX (" Feuerstein ") -\u003e" F623 "Soundex (" Feuerst ") -\u003e" F623 "

When using the SOUNDEX function, several rules should be remembered:

• The SOUNDEX value always starts with the first input line letter.
• The return value is generated only by the first five consonant in the string.
• To calculate the digital part, SOUNDEX uses only consonants. All vowels in the line, except the initial, are ignored.
• The Soundex function ignores the symbol register; For the letters of the upper and lower register, the same SOUNDEX values \u200b\u200bare generated.

The Soundex function is useful for queries in which the exact writing of the value in the database is unknown or cannot be easily definitely.

Soundex algorithm is focused on english; In other languages, it can work badly (or not working at all).

• Substr (line1, initial_position, length)
  Returns a substring from the string1, which begins with the initial_position and has a given length. If the number of characters to the end of the line1 will be less than the length, all characters from the initial position before the end of the line are returned. The function has the following varieties:
• Substr (line1, initial_position)
  Returns all characters from the initial_position to the end of the line1.
• Substr (line1, negative_nachable_Position, length)
  The initial position of the substring is counted from the end of the line1.
• Substr (line1, negative_nachable_Position)
  Returns the latest ABS (negative_nachable_position) strings.

The SUBSTR function considers the string as a sequence of characters. Its species substrb, substr2 and substr4 view the string as a sequence of bytes, code units (Code Points) unicode, respectively. The type of substrc considers the string as a sequence of full symbols of Unicode. For example, the string "A \\ 0303", which is a decomposed equivalent "\\ 00E3", or a, is considered as one character. On the contrary, the SUBSTR function considers "A \\ 0303" as a sequence of two characters.

• To_char (National_Simvol_Dany)
  Converts data from a national symbol set to an equivalent view in a database symbol set. Also, see to_nchar.

The to_char function can also be used to convert the date / time and numbers in the steady form.

• To_multi_Byte (line1)
  Converts single-byte characters in their multibyte equivalents. In some multibyte encodings, and above all UTF-8, there can be several options for representing one character. For example, in UTF-8, the presentation of the letter "G" may contain from 1 to 4 bytes. To go from a single-byte view to multibyte, the to_multi_byte function is used. This feature is reverse to the to_single_byte.
• To_nchar (symbols_v_nabel__data)
  Converts data from a database symbol set to an equivalent view in a national character set. Also, see to_char and Translate ... Using.

The to_nchar function can also be used to convert the date / time and numbers to the steamed form.

• To_Single_Byte (line1)
  Converts multibyte characters into their single-byte equivalents. The function is reverse to the to_multi_byte.
• Translate (line1, desired_nabot, set_names)
  Replaces in line 1 each occurrence of the character from the desired_naber, the corresponding symbol of dialing. Example:

Translate ("ABCD", "AB", "12") -\u003e "12cd"

If the desired_nabon contains more characters than a set of selects, "extra" symbols that do not have conformity in the order set are not included in the result. Example:

Translate ("abcdefg", "abcd", "zyx") -\u003e "zyxefg"

The letter "D" is deleted, because it is present in the artistic_nabloral, but does not have the equivalent in the set. The translate function replaces individual characters, and the Replace function is entire lines.

• Translate (USING CHAR_CS text) and Translate (text using nchar_cs)
  Converts character data to a database symbol set (char_cs) or a national character set (NCHAR_CS). The output data type will be VARCHAR2 or NVARCHAR2, depending on whether the conversion to a set of database characters or a national character set, respectively.

The Translate function ... Using is one of the SQL functions according to the ISO standard. Starting with Oracle9i Release 1, you can simply assign the value of VARCHAR2 variable type nvarchar2, and vice versa - the system will implicitly perform the desired conversion. If you want to perform conversion explicitly, use the to_char and to_nchar functions to convert text to a database symbol set and a national character set, respectively. Oracle recommends using the specified functions instead of translate ... using, because they support a wider set of data input types.

• Trim (FROT line1)
  Returns the string obtained as a result of removal from the line1 of all initial and final gaps. The function has the following varieties:
• Trim (LEADING FROM ...)
  Deleting only initial gaps.
• Trim (Trailing From ...)
  Deleting only end gaps.
• Trim (Both From ...)
  Removal both start and finite spaces (used by default).
• Trim (... Removable_Simal FROM String1)
  Removing the occurrences of one removable_simil to the selection of a programmer.

The Trim function was enabled in Oracle8i to provide more complete compatibility with ISO SQL. It combines the functionality of Ltrim and Rtrim, but differs from them by the fact that TRIM allows you to set only one removable symbol, whereas when using Ltrim and RTRIM, you can set a set of remote characters.

• Unistr (line1)
  Returns the string1 converted to Unicode; Thus, the function is reverse with respect to ASCIISTR. To represent non-printable characters in the input line, you can use the record \\ xxxx, where XXXX is the symbol code index in Unicode. Example:

Begin dbms_output.put_line (Unistr ("Euro Sign \\ 20ac")); End; Euro sign €.

Function provides convenient access To the whole set of symbols of Unicode, including those that cannot be entered directly from the keyboard.

• Upper (line1)
  Converts all the letters of a given string to the top register. The type of return value matches the type of input data (Char, Varchar2, CLOB). The function is reverse with respect to Lower. Also, see NLS_UPPER.

The functions that we will discuss in this part typically use the built-in PL / SQL code grouped into packages and supplied Oracle. Some handle numerical, character values \u200b\u200band date values, others convert data to different types data. Functions can use nested calls and some functions are designed to work with the NULL value. Case and Decode conditions can display a different result depending on data values, which provides the ability to branch in the context of SQL request

Functions are divided into two large groups: those that calculate values \u200b\u200bfor each line, and those that execute one calculation for all lines. We will look at the conversion features, functions for working with symbolic data, numeric data and data type.

Definition of function

The function is a program that can take (but optionally) input parameters, perform any operations and return the literal value. The function returns only one call value.

Three important components form a function defining basis. The first is a list of input parameters. It defines zero or more parameters that can be transmitted for processing. These parameters, or arguments, may be optional (to be default) and be different data types. The second component is the type of data of the impact result. After execution, only one value of the predefined data type is returned by the function. The third component encapsulates the parts of the processing performed by the function and contains program codewhich works with input parameters, makes calculations and returns the value.

The function is often described as a black box that takes the input data, makes something and returns the result. Instead of focusing on the items of functions, it is more useful to understand which functionality provides built-in functions.

Calling functions can be embedded, for example, as F1 (X, Y, F2 (A, B), Z), where F2 features two input parameters and returns the third of the four parameters for the F1 function. Functions can work with any data types: the most commonly used symbolic and numeric data, as well as data type data. These function parameters can be columns or expressions.

As an example, you can consider the function that calculates the age of a person. The AGE feature takes only one parameter, birthday. The result is returned by the AGE function is the number of man reflecting age. Calculations of the black box fall into obtaining a difference in years between the current date and birthday transmitted as an input parameter.

Types of functions

Functions can be globally divided into two categories: processing string (lowercase functions) and processing rows set (grouping functions). This allocation is very important for understanding the context where various functions are used.

Line functions

Several types of lowercase functions are available, including string functions, operation functions with numbers, dates, type conversion functions general functions. These functions handle one row from the set at the time of time. If the query selects ten strings, the function will be performed ten times, one time for each row with the possible use of row column values \u200b\u200bas input function parameters.

The following query selects two columns from the Regions table and expression using the Length function and the Region_name column

select Region_ID, Region_Name, Length (Region_name) from regions;

The value of the Region_name column value is calculated for each of the four lines in the Regions table; The function is performed four times, returning the literal value each time.

Line functions operate work with data elements for sampling and formatting them before display. Input values \u200b\u200bof the lowercase function can be a user-defined constant or literal, column data, variables or expressions, possibly using nested lowercase functions, etc. Nested calls are often used technique. Functions can return the value of the data type different from the data type of input parameters. The discontinuing request shows how the Length function takes the input value of the string and returns the number.

In addition to using functions in the Select section, lowercase functions can be used in the WHERE and ORDER BY sections.

Data set features

As you can guess from the name, these functions operate more than one line. The typical use of a multitro-function is the calculation of the amount or average value of a numerical column or counting the number of rows as a result. Such functions are sometimes called grouping functions, and we will look at them in the next chapter.

Use of functions changing register

Data in tables can be filled from various sources: programs, crypts, and so on. Do not rely that symbolic data will be entered in a predetermined register. String functions that change the register are intended for two important tasks. They can be used, firstly, to change the data register while saving or output information, or under WHERE conditions for a more flexible search. It is much easier to search for a string using a fixed register, the amount of checks of all combinations of the upper and lower register. Remember that the call to the functions does not change the data that is stored in the table. They convert the result of the query result.

Input parameters may be character literals, data type columns, symbolic expressions or numbers and dates (which implicitly converted into strings).

Lower function

The Lower function replaces all the symbols of the registration register to the equivalent symbols of the line register. Syntaqis function Lower (String). Consider an example of a request that uses these functions.

sELECT Lower (100 + 100), Lower ('SQL'), Lower (Sysdate) from dual

We are moving that the current date is December 17, 2015. The result of the query will be the lines' 200 ',' sql 'and '17 -dec-2015'. Numerical expression and date are implicitly converted to the string before calling the Lower function.

In the following example, the Lower function is used to search for rows where the letters 'u' and 'R' in any register go on each other

select First_Name, Last_Name, Lower (Last_Name) from Employees

where Lower (Last_Name) Like '% ur%';

You can write a similar request without using the Lower function. For example

select First_Name, Last_Name from Employees

wHERE LAST_NAME LIKE '% UR%' OR LAST_NAME LIKE '% UR%'

oR Last_Name Like '% Ur%' or Last_name Like '% UR%'

This request works, but it is too cumbersome, and the number of OR operators increases exponentially as the row increases.

Upper function

Upper Function The logical opposite of the Lower function and replaces all lowercase characters on their capital equivalents. Syntax function - Upper (String). Consider example

sELECT * from Countries Where Upper (Country_name) Like '% U% S% A%';

This query selects rows from the Countries Table where Country_name contains the letters 'u', 's', 'a' in any register in this order.

Initcap feature

The initcap function is often used to display data. The first characters of each word in the row are converted to the upper register, all other characters are converted into lowercase equivalents. Under the word, it means a set of characters that do not contain spaces and specialsimeters. Space, eyelid symbol as well as special mysters such as percent sign, exclamation mark, dollar sign is regarded as dividers. The initcap feature receives one parameter and Initcap syntax (String). The following example shows an example of using the INITCAP function.

select Initcap ('init Cap or Init_cap or init% Cap') from dual

The result of this request will be the line init CAP or init_cap or init% Cap

Use of running functions

Work functions with rows One of the most powerful features provided by Oracle. They are very helpful and understandable almost without detailed explanations and are very often used by different programmers when processing data. The attached challenges of these functions are often used. Concatentation operator can be used instead of the Concat function. The Length, Instr, Substr and Replace features can complement each other, as well as RPAD, LPAD and Trim.

Concat function

The Concat function combines two literals, columns or expressions to draw up one large expression. The Concat function has two input parameters. The syntax of the Concat function (String1, String2) where String1 and String2 can be a literal, column or expression of which symbol literal. The following example shows the use of the Concat function.

select Concat ('Today IS:', Sysdate) from dual

The second function parameter is the SYSDATE function that returns the current system time. The value is converted to the string and the first parameter is attached to it. If the current system date is December 17, 2015, the query will return the 'Today IS: 17-DEC-2015' string.

Consider how to use the function to combine three elements. Since the Concat function can only receive two input parameters, you can merge only two elements. In this case, you can use the function call as a function of a function call function. Then the request will look like

select Concat ('Outer1', Concat ('Inner1', 'Inner2')) from dual;

The first feature has two parameters: the first parameter is the literal 'Outer1', and the second parameter is the attached Concat function. The second function takes two parameters: literal 'inner1' and literal 'Inner2'. The result of this request will be the 'Outer1 Inner1 Inner 2' string. Invested functions Consider a little later.

Length

The Length function returns the number of characters that make up the string. Spaces, tabulation and special characters are taken into account by the Length function. The function has one parameter and syntax Length (String). Consider the request

sELECT * from Countries Where Length (Country_Name)\u003e 10;

The Length function is used to select those countries in which the length length is greater than ten characters.

RPAD functions and lpad.

The RPAD and LPAD functions return the fixed length string and, if necessary, complement the source value with a specific set of symbols to the left or right. The characters used to add may be literal, the value of the column, expression, space (default value), tabulation and special characters. LPAD and RPAD functions take three input parameters and LPAD syntax ( s., N.P.) and rpad ( s., N.P.) where s. - the value of the string for processing, n. - the number of characters of the result and p. - Symbols for adding. If LPAD is used, then symbols p. Add to the left until the length of n. If rpad is right. Note that if the length s. More than Length n. - then the result will be the first n. Symbols of value s.. Consider queries in Figure 10-1

Figure 10-1 - Using RPAD and LPAD functions

The first query does not change the data and the result is not very readable compared to the result of the second request. RPAD is used to add spaces there where necessary for first_name and last_name so that all values \u200b\u200bwere fixed length in 18 characters, and LPAD is used to add spaces to the beginning of the Salary value until the length of 6 characters is reached.

Trim feature

The Trim function removes the characters and starts or ending the line to make it potentially shorter. The function accepts the required parameter and optional. TRIM function syntax ([ trailing.| Leading| Both.] trimString from. string). Parameter Input string (S) required. The following items list the parameters

• Trim (s) gaps at the beginning at the end of the line
• TrimString from S) removes trimgstring symbols at the end of the string
• TrimString from S) removes trimgstring characters at the beginning of the line
• TrimString from S) or TrimString from S) remove all TrimString characters at the beginning and at the end of the row

sELECT TRIM (Both '*' from '**** hidden ****'),

trim (Leading '*' from '**** hidden ****'),

trim (Trailing '*' from '**** hidden ****') from dual;

Return "Hidden", "Hidden ****", and "**** Hidden." Please note that by specifying only one character, all characters are cleaned if they repeatedly repeated.

Instr. function

Instr's function is looking for a substring in the string. Returns the number denoting the position from where the N-Idge begins, starting from the search position, relative to the beginning of the string. If the substring is not found in the string - returns 0.

The Instr function has two parameters of mandatory and two options optional. The syntax function Instr (Source String, Search String ,,). The default value for Search Start Position \u003d 1 or in other words, the beginning of the Source String string. The default value for N occurrence \u003d 1 or the first entry. Consider a few examples

Query 1: Select Instr ('1 # 3 # 5 # 7 # 9 #', '#') from dual;

Query 2: Select Instr ('1 # 3 # 5 # 7 # 9 #', '#', 5) from Dual;

Query 3: Select Instr ('1 # 3 # 5 # 7 # 9 #', '#', 3, 4) from dual;

The first request is looking for the first entry of the hash tag in the string and returns the value of 2. The second request is looking for a hash tag in a string from the fifth symbol and finds the first entry from 6 characters. The third request is looking for the fourth entry of the hash tag starting from the third symbol and finds it in position 10.

SUBSTR function

The Substr function returns the substitute for a certain length from the source string starting from a specific position. If the initial position is greater than the length of the source line - the NULL value is returned. If the initial string lengths are not enough to obtain the value of the required length, starting from a specific position, the part of the line from the source character is returned to the end of the line.

The Substr function has three parameters, the first two are required and the SUBSTR syntax (Source String, Start Position,). The default value for Characters to Extract \u003d The difference between the Source String and Start Position. Consider the following examples

Query 1: Select Substr ('1 # 3 # 5 # 7 # 9 #', 5) from dual;

Query 2: Select Substr ('1 # 3 # 5 # 7 # 9 #', 5, 3) from dual;

Query 3: Select Substr ('1 # 3 # 5 # 7 # 9 #', -3, 2) from dual;

Request 1 Returns Monster Starting from Position 5. Since the third parameter is not specified, the number of characters is equal to the length of the source line minus the starting position and will be six. The first request will return the substring '5 # 7 # 9 #'. Request Two Returns Three Symbols Starting from the Fifth Symbol and the string result will be '5 # 7'. The query three begins with a minus three. Negative initial position says Oracle that the initial position is calculated from the end of the line. Thus, the initial position will be the length of the minus three and equal to 8. The third parameter is two and returns the value '# 9'.

REPLACE function

The REPLACE function replaces all the entry of the desired element to the row value for the substitution. If the length of the replaceable item is not equal to the length of the element to which the replacement is replaced, the length of the resulting string will be different from the source line. If the desired substring is not found, the string is returned unchanged. Three parameters are available, the first two mandatory and call syntax Replace (Source String, Search Element,). If we clearly not specify the Replace Element parameter, all the searches of the Search Element are removed from the source line. In other words, Replace Element is equal to an empty string. If all source string characters are replaced with empty Replace Element returns NULL. Consider several requests

Query 1: Select Replace ('1 # 3 # 5 # 9 #', '#', '-\u003e') from dual

Query 2: Select Replace ('1 # 3 # 5 # 9 #', '#') from dual

Query 3: SELECT REPLACE ('#', '#') from dual

Hash in the first query indicates a character for searching and string to replace '-\u003e'. The hash appears in the line five times and is replaced, we obtain the final string '1-\u003e 3-\u003e 5-\u003e 7-\u003e 9-\u003e'. Request 2 does not indicate a clear line for replacement. The default value is an empty string and the result will be '13579'. Request number three will return NULL.

Use of numerical functions

In Oracle, there are many built-in features to work with numbers. An essential difference between the numerical function and others is that these functions are received by parameters only numbers and return only numbers. Oracle provides numerical functions to work with trigonometric, exponential and logarithmic expressions and with many others. We focus on simple numerical lowercase features: Round, Trunc and MOD.

Round function

Round function rounds the number depending on the accuracy required. The return value is rounded or large or in a smaller side, depending on the value of the last digit in the required discharge. If the value of the accuracy n, then the figure that will be rounded will be on the position n after the comma, and the value will depend on the number on the position (n + 1). If the accuracy value is negative, then all numbers after discharge N on the left of the semicolons will be 0, and the value N will depend on N + 1. If the value of the number on which the rounding depends more than or equal to 5, the rounding occurs in a major side, otherwise it is smaller.

The Round feature receives two input parameters and Round syntax (Source Number, Decimal Precision). Source Number can be any number. The Decimal Precision parameter determines the desired accuracy and optional. If this parameter is not specified, the default value will be 0, which means the need for rounding to the nearest integer.

Consider Table 10-1 for Numbers 1601.916. The negative values \u200b\u200bof the accuracy are on the left of the point (the whole part), when the positive is considered to the right of the point (fractional part).

If the accuracy value unit, the value is rounded up to a tent. If two, the value is rounded to the second order, etc. The following requests displays the use of this function.

Query 1: Select Round (1601.916, 1) from dual;

Query 2: SELECT ROUND (1601.916, 2) from dual;

Query 3: Select Round (1601.916, -3) from dual;

Query 4: Select Round (1601.916) from dual;

The first request uses the accuracy parameter equal to one, which means that the number will be rounded to the nearest tenth. Since the value of the cell is equal to one (less than 5), then rounding is rounded in a smaller side and the value of 1601.9 is returned. The accuracy of the second query is equal to the twice, thus the value is surrounded to the cell. Since the value of the thousandth part is 6 (which is more than 5), the value of the cellular part is rounded up and returns the value of 1601.92. The value of the accuracy parameter in the third request is minus three. Since the value is negative, it means that rounding will occur based on the value of the third position on the left of the point, in the second category (hundreds), and value 6. Since 6 more than five, then rounding up and returns the value of 2000. Request 4 Causes a function without the accuracy parameter. This means that the number is rounded to the nearest whole. Since the tenth part is 9, the value is rounded in a large side and a value of 1602 is returned.

Numerical function trunc

Fuction TRUNC reduces the value of the number based on the value of the accuracy parameter. The reduction differs from rounding the fact that with the reduction of an extra part simply cuts off and no changes occurring the remaining numbers. If the accuracy value is negative, the input value is reduced to the position on the left of the comma. TRUNC feature syntax (Source Number, Decimal Precision). The Source Number parameter may be any number and this parameter is required. The Decimal Precision parameter determines the rounding position and is not required, the default value will be zero, which means a reduction to an integer.

If the value of Decimal Precision is equal to one, the number is reduced to the tenths, if two, then to hundredths and so on. Consider several examples of using this feature.

Query 1: Select Trunc (1601.916, 1) from dual;

Query 2: Select Trunc (1601.916, 2) from dual;

Query 3: Select Trunc (1601.916, -3) from dual;

Query 4: SELECT TRUNC (1601.916) from dual;

Inquiry 1, accuracy is used equal to one, which means the reduction of the value to the tenths and returns to 1601.9. The accuracy in the second query is two, the initial value is reduced to hundredths and returns to 1601.91. Please note that the value obtained will differ from the value returned by the Round function with the same parameters, since when calling Round it will occur to the large side (6 more 5). In the query number three uses a negative number as the value of the accuracy parameter. Position three on the left of the comma means that the reduction will be up to the third category (hundreds are reduced) as shown in Table 10-1 and the return value will be 1000. And finally, in the fourth request, the value of the accuracy is clearly unspecified and the fractional part of the initial number is clearly unspecified. The result will be 1601.

Mod function

The MOD function returns the balance of the division. Two numbers, divisible (the number that is divided) and the divider (the number to which is divided) is defined as parameters and the division operation is calculated. If divisible is divided into the divider, it is returned to zero, because there is no residue. If the divider zero, then the fission error does not occur on zero, but is returned to the divi. If the divider is more than divisible, Delimi is returned.

The MOD function has two input parameters and syntax mod (dividend, divisor). The Dividend and Divisor parameters can be numerical literals, columns or expressions and can be positive or negative. The following examples show the use of this function.

Query 1: SELECT MOD (6, 2) FROM DUAL

Query 2: SELECT MOD (5, 3) FROM DUAL

Query 3: Select MOD (7, 35) from Dual

Query 4: Select MOD (5.2, 3) from Dual

In the query, one 6 is divided into two aimed without a residue and returns 0. In the query, two 5 is divided into 3, the whole part will be 1 and returns the residue 2. In the query number three seven is divided by 35. Since the divider is more than divisible - Delimi those. Whole part 0. The query four uses a fractional number as divisible. The whole part will be one and the residue will be 2.2.

Any one-to-one is divided into two without a residue, any odd number when dividing two will return the residue 1. Therefore, the MOD function is often used to distinguish even and odd numbers.

Work with dates

Features working with dates offer a convenient way to solve tasks related to dates without having to take into account the high-year years, how many days in a particular month. First, we consider how data type and date formatting are stored, as well as the SYSDATE function. Consider the functions of the Add_Months, Months_between, Last_Dat, Next_Day, Round and Trunc.

Storage dates in the database

The database stores data as a number that is capable of maintaining the calculation of the century, year, month and day, as well as information about the time, such as hour, minute and second. When a data query occurs, a certain formatting (mask) is superimposed, and the default mask displays the day, the first three letters of the month name and two digits displaying the year.

SYSDATE function

The SYSDATE function does not use the input parameters and returns the current time and date set on the database server. By default, the SYSDATE function returns a DD-MON-RR date and displays the date on the server. If the server is installed in another time zone than the client machine, the time and date returned by the SYSDATE may differ from local values \u200b\u200bon the client machine. You can execute such a request to display the system date on the server.

sELECT SYSDATE FROM DUAL

Arithmetic over dates

The equation relates the most important principle when working with dates

DATE1 - DATE2 \u003d NUM1

Date can be deducted from another date. The difference between the two dates is understood as the number of days between them. Any number, including fractional, can be added or deducted from the date. In this context, the number is the number of days. The amount or difference between the number and date is always the date. This principle implies that addition, multiplication or division of two dates is impossible.

MONTHS feature_Between.

The MONTHS_BETWEEN function returns the number of months between two mandatory input parameters. The syntax of the MONTHS_BETWEEN function (DATE1, DATE2). The function calculates the difference between DATE1 and DATE2. If DATE1 is less than DATE2, then a negative number is returned. The return value may consist of a whole part reflecting the number of months between two dates and a fractional part reflecting how many days and hours remains (based on a month equal to 31 days) after the deduction of a whole month. The integer is inspired if the day of the most compared months is the same or last day of the corresponding month.

The following examples are used by the pounds of MONTHS_BETWEEN

Query 1: Select Months_between (Sysdate, Sysdate-31) from Dual;

We are breeding that the current date is April 16, 2009. The request one will return one as the number of months between April 16, 2009 and March 16, 2009. The query two implicitly converts literal literals using DD-MON-YYYY format. Since part of the time omitted Oracle will set the value of the time 00.00.00 for both dates. The fuchation will return the value approximately equal to 1.03225806. The whole part of the result denotes that between dates one month. Between February 28 and March 28, exactly one month. Then the fractional part should show exactly one day. The result includes a clock minute and seconds, but in our case the temporal component of the dates is the same. Multiplication 0.03225806 to 31 will return 1, since the fractional part returned by Monhs_between is calculated, allowing that the month is equal to exactly 31 days. Therefore, the number of the number three will return the value of 32.

A popular error is the assumption that the returned data type function depends on the type of function (the function of working with the date must return the date, row processing functions - string). This is true only for numerical functions. Symbolic features and dates function can return the value of any data type. For example, Instr appears symbolic function, and Monts_between the function of working with the date, but both of them return the result. Also often mistakenly reason that the difference between dates is the date when it is actually the number.

ADD feature_Months.

The ADD_MONTHS function returns the date received by adding a certain number of months to the source date. This feature has two required parameters and the ADD_MONTHS syntax (Start Date, Number of Monhs). The Number of Months parameter value may be negative, then the initial value will decrease on this number of months and fraction, but only a whole part will be taken into account. The following three requests show the use of the Add_Months feature.

The result of the first request will be the same day on May 7, 2009, as the day remains the same if it is possible and the month increases by one. In the second request, the number of months is fractional, which is ignored, that is, this query is equal to Add_months ('31 -dec-2008 ', 2). Adding two months should return the 31-Feb 2009, but this date does not exist, so the last day of the month is returned. In the last example, a negative number is used for the number of monthly months and returns the date 07-APR-2008 that for twelve months earlier than the initial value.

NEXT Function_Day

The next_date function returns the next closest day of the week after the initial date. This feature has two required parameters and syntax NEXT_DAY (Start Date, Day of the Week). The function of the event is the value when the specified day of the week will occur after Start Date. The dae of the Week parameter can be specified both by the number and string. Permissible values \u200b\u200bare determined by the NLS_DATE_LANGUAGUAGUAGUA parameter and the default are three first letters of the day of the week in any register (Sun, Mon etc) or integers where 1 is Sunday, 2 - Monday and so on. Also, the names of the days of the week may be more than three symbols; For example, Sunday can be specified as Sun, Sunday, Sunday. Consider several requests

January 1, 2009 this is Thursday. The next Tuesday will be in 5 days, January 6, 2009. The second request will return January 7, 2009 - Next Wednesday after January 1. The third request uses a number as the parameter and if you have American values \u200b\u200binstalled, then the fifth day is Thursday. Next Thursday after January 1, exactly a week - January 8, 2009.

Last function_Day

The Last_Day function returns the date of the last day of the initial date. This feature requires one required parameter and syntax Last_Day (Start Date). The function chooses the month of the original date and then calculates the last day of the month. The following request will return January 31, 2009

Round function To work with dates

The Round function rounds the date value to the specified date accuracy. Return value is rounded either to more or R smaller value depending on the value of the rounded item. This feature requires one mandatory parameter and allows one optional and syntax of the Round function (Source Date). The Source Data parameter may be any data type type type. The Date Precision Format parameter determines the rounding level and the default value is the day. The Date Precision Format parameter can be a century (CC) year yyyy quarter Q month M week W day DD hour HH minute Mi.

Rounding to century is equivalent to adding a unit to the current century. Rounding up to the month will be in the biggest if the day is more than 16 otherwise there will be rounding until the first day of the month. If a month from one to six rounding will be before the start of this year, otherwise the date of the beginning of the next year will be back. Consider the request

Suppose this request was completed on April 17, 2009 at 00:05. At first, rounding the current date before the day (the accuracy parameter is clearly unspected). Since time 00:05 The day is not rounded in the big party. So as April 1, 2009 this is a medium, then the second column will return the Wednesday of the week in which the original date includes. The first environment of the week, which includes April 19 - this is April 15, 2009. The third column comes the month to the next one (as 17 more than 16) and returns May 01, 2009. The half-day column rounds the date until the year and returns 1 of 2009, since April is a 4th month.

Trunc Function when working with dates

The Trunc function reduces the date based on the accuracy parameter. This feature has one parameter mandatory and one and the TRUNC call syntax (Source Date,). The Source Date parameter can be any valid date. The Date Precision Format parameter determines the level of reduction of the date and optional, the default value is reduced to day. This means that all time values \u200b\u200bare reset - 00 hours 00 minutes 00 seconds. Abbreviation to the month will return the date equal to the first day of the month of the initial date. Reducing up to a year - will return the first day of the original date. Consider a request using a function with different parameters.

This request is performed on April 17 at 00:05. The first column reduces the system date until day, the time is converted from 00:05 at 00:00 (the accuracy parameter is clearly unspecified, the default value is used) and the current day is returned. The second column reduces the date before the same day of the week, which was the first day of the month (Wednesday) and returns the Wednesday of the current week - April 15. The third column reduces the date up to the month and returns the first day of the month - April 1. The fourth column reduces the date until the year and returns the first day of the year.