The basic functions in Oracle ( P3 )

View: 754    Dowload: 0   Comment: 0   Post by: naruto   Category: Oracle   Fields: Other

0 point/2 review File has been tested

 

The basic functions in Oracle ( P3 ) Blog / MSSQL database - oracle - This article is a sequel of the song " The basic functions in Oracle ( P2 ) " . In this section alone will solve all the functions and character strings in group 1 . Are you ready yet , let's get started.
This article is a sequel of the song " The basic functions in Oracle ( P2 ) " . In this section alone will solve all the functions and character strings in group 1 . Are you ready yet , let's get started.
 

13 . LOWER ( string1 )
a) Function : convert to lowercase characters .
 
b ) Syntax : lower ( string1 )
 
string1 is the string to convert to lowercase .
 
c ) application version : Oracle 8i , Oracle 9i , Oracle 10g , Oracle 11g
 
d ) For example :
lower ( ' Design and Development ' ) results : ' design and development '
lower ( CWEBVN '2 ' ) results : '2 cwebvn '
 

14 . LPAD ( string1 , padded_length , [ pad_string ] )
a) Function : more on the left side of a string with a specific set of characters ( if string1 is not null ) .
 
b ) Syntax lpad ( string1 , padded_length , [ pad_string ] )
 
string1 is the string to add ( left hand side ) .
padded_length is the number of characters to return. If padded_length smaller than the original string , LPAD function will truncate the string to size padded_length .
pad_string parameter is optional . This is the string will be padded on the left hand side of string1 . If this parameter is omitted , the function will LPAD space on the left side of string1 .
 
c ) application version : Oracle 8i , Oracle 9i , Oracle 10g , Oracle 11g
 
d ) For example :
LPAD ( CWEB '2 ' , 7 )                       results : ' 2cweb '
LPAD ( CWEB '2 ' , 2 )                    results : '2 c '
LPAD ( CWEB '2 ' , 8 , '0 ' )               results : '0002cweb '
LPAD ( cweb.vn '2 ' , 8 , ' W ' )                  results : '2 cweb.vn '
LPAD ( cweb.vn '2 ' , 10 , ' W ' )              results : ' WW2cweb.vn '
 

15 . LTRIM ( string1 , [ trim_string ] )
a) Function : removes all specified characters from the left hand side of a string .
 
b ) Syntax: LTRIM ( string1 , [ trim_string ] )
 
string1 is a string of characters cut from the left hand side .
trim_string the chain will be removed from the left hand side of string1 . If this parameter is omitted , the LTRIM function to remove all spaces from string1 trangn .
 
c ) application version : Oracle 8i , Oracle 9i , Oracle 10g , Oracle 11g
 
d ) For example :
LTRIM ( ' 2cweb ' )                                     results : '2 CWEB '
LTRIM ( ' 2cweb ' , ' ' ) ;                            Results : CWEB '2 '
LTRIM ( '000123 ' , '0 ' )                               results : '123 '
LTRIM ( '123123Web2C ' , '123 ' )                  results : ' Web2C '
LTRIM ( '123123Web2C123 ' , '123 ' )                results : ' Web2C123 '
LTRIM ( ' xyxzyyy2cwebvn ' , ' xyz ' )                 results : '2 cwebvn '
LTRIM ( '6372Web2C ' , '0123456789 ' )                    results : ' Web2C '
 

16 . REPLACE ( string1 , string_to_replace , [ replacement_string ] )
a) Function : to replace a series of characters in a string with a different set of characters .
 
b ) Syntax: replace ( string1 , string_to_replace , [ replacement_string ] )
 
string1 is the string to replace a sequence of characters with a different set of characters .
string_to_replace the string will be searched in string1 .
replacement_string is optional . All occurrences of string_to_replace will be replaced by replacement_string in string1 . If replacement_string parameter is omitted , the function will remove all occurrences of string_to_replace , and returns the resulting string .
 
c ) application version : Oracle 8i , Oracle 9i , Oracle 10g , Oracle 11g
 
d ) For example :
replace ( '123123web ' , '123 ' ) ;                           Results : ' web '
replace ( '123web123 ' , '123 ' ) ;                            Results : ' web '
replace ( '222web222 ' , '2 ' , '3 ' ) ;                       Results : '333web333 '
replace ( '0000123 ' , '0 ' ) ;                                     Results : '123 '
replace ( '0000123 ' , '0 ' , ' ' ) ;                         Results : ' 123 '
 

17 . RPAD ( string1 , padded_length , [ pad_string ] )
a) Function : more on the right side of a string with a specific set of characters ( if string1 is not null ) . In contrast to the LPAD function .
 
b ) Syntax: RPAD ( string1 , padded_length , [ pad_string ] )
 
string1 is the string to add ( right hand side ) .
padded_length is the number of characters to return. If padded_length smaller than the original string , LPAD function will truncate the string to size padded_length .
pad_string parameter is optional . This is the string will be padded on the right hand side of string1 . If this parameter is omitted , the function will LPAD space on the right side of string1 .
 
c ) application version : Oracle 8i , Oracle 9i , Oracle 10g , Oracle 11g
 
d ) For example :
RPAD ( CWEB '2 ' , 7 )                   results : '2 CWEB '
RPAD ( CWEB '2 ' , 2 )                     results : '2 c '
RPAD ( CWEB '2 ' , 8 , '0 ' )             results : '2 cweb000 '
RPAD ( cweb.vn '2 ' , 8 , ' W ' )                results : '2 cweb.vn '
RPAD ( cweb.vn '2 ' , 10 , ' W ' )              results : '2 cweb.vnWW '
 

18 . RTRIM ( string1 , [ trim_string ] )
a) Function : removes all specified characters from the right side of a string .
 
b ) Syntax rtrim ( string1 , [ trim_string ] )
 
string1 is a string of characters cut from the right hand side .
trim_string the chain will be removed from the right side of string1 . If this parameter is omitted , the function RTRIM removes all trailing spaces string1 .
 
c ) application version : Oracle 8i , Oracle 9i , Oracle 10g , Oracle 11g
 
d ) For example :
rtrim ( CWEB '2 ' ) ;                                          Results : CWEB '2 '
rtrim ( CWEB '2 ' , ' ' ) ;                                      Results : CWEB '2 '
rtrim ( '123000 ' , '0 ' ) ;                                   Results : '123 '
rtrim ( ' web2c123123 ' , '123 ' ) ;                    Results : ' web2c '
rtrim ( '123web2c123 ' , '123 ' ) ;                     Results : '123web2c '
rtrim ( ' Web2Cxyxzyyy ' , ' xyz ' ) ;                Results : ' Web2C '
rtrim ( ' Web2c6372 ' , '0123456789 ' ) ;             Results : ' Web2c '
 

19 . SOUNDEX ( string1 )
a) Function : returns a phonetic representation ( sound ) of a string .
 
b ) Syntax: SOUNDEX ( string1 )
 
string1 is the phonetic value chain will be returned .
Soundex algorithm is as follows :
- Soundex returns always start with the first letter of string1 .
- Soundex function using only the first five consonants to determine the part number of the return value , except if the first letter is a vowel sound of string1 .
- Function Soundex not distinguish uppercase and lowercase letters . This means that both upper and lower case letters will usually return the same Soundex .
 
c ) application version : Oracle 8i , Oracle 9i , Oracle 10g , Oracle 11g
 
d ) For example :
SOUNDEX ( cweb.vn '2 ' )                                results : ' C115 '
SOUNDEX ( CWEB.VN '2 ' )                           results : ' C115 '
SOUNDEX ( ' apples ' )                         results : ' A142 '
SOUNDEX ( ' apples are great ' )                results : ' A142 '
SOUNDEX ( ' applus ' )                         results : ' A142 '
 

20 . SUBSTR (string , start_position , [ length ] )
a) Function : Allows you to extract a substring from a string . ( cut the chain )
 
b ) syntax : substr (string , start_position , [ length ] )
 
string is the source string .
start_position is positioned to exploit . The first position in the string is always 1 .
length parameter is optional . This is the number of characters to extract ( cut ) . If this parameter is omitted , substr will return the entire string .
Note:
- If start_position is 0 , the function substr to understand is start_position is 1 ( the first position in the string ) .
- If start_position is a positive number , then substr starts from the beginning of the string .
- If start_position is a negative number , then substr starts from the end of the string and countdown .
- If length is a negative number , then substr will return a NULL value .
 
c ) application version : Oracle 8i , Oracle 9i , Oracle 10g , Oracle 11g
 
d ) For example :
substr ( ' Design and Development ' , 3 , 4 ) results : ' sign '
substr ( ' Design and Development ' , 3 ) results : ' sign and development '
substr ( Cwebvn '2 ' , 1 , 5 ) result : CWEB '2 '
substr ( Cwebvn '2 ' , -3 , 3 ) results : ' bvn '
substr ( Cwebvn '2 ' , -6 , 4 ) results : ' CWEB '
 

21 . TRANSLATE ( CHUỖI1 , STRING_TO_REPLACE , REPLACEMENT_STRING )
a) Function : to replace a series of characters in a string with a character set . However, it replaces a single character at a time .
 
b ) Syntax: translate ( string1 , string_to_replace , replacement_string )
 
string1 is the string to replace a sequence of characters with a set of characters ( string source ) .
string_to_replace the string will be searched in string1 .
replacement_string - All characters in string_to_replace will be replaced by the corresponding character in replacement_string .
 
c ) application version : Oracle 8i , Oracle 9i , Oracle 10g , Oracle 11g
 
d ) For example :
TRANSLATE ( tech23 '1 ' , '123 ' , '456 ' ) results : '4 tech56 '
TRANSLATE ( '222tech ' , '2 ec ' , '3 it ' ) results : '333tith '
 

22 . TRIM ( [ leading | trailing | Both [ trim_character ] ] string1 )
a) Function : cutting removes all specified characters at the beginning or end of a string of charge .
 
b ) Syntax: trim ( [ leading | trailing | Both [ trim_character ] ] string1 )
 
string1 is the string to trim .
leading - in the first elimination trim_string string1 .
trailing - trim_string removed at the end of string1 .
Both - remove trim_string at the beginning and end of string1 .
If we can not choose parameter leading , trailing or Both , then this function will remove trim_string from both the beginning and end of string1 .
trim_character the characters will be removed from string1 . If this parameter is omitted , this function will remove all the spaces at the beginning and end of string1 .
 
c ) application version : Oracle 8i , Oracle 9i , Oracle 10g , Oracle 11g
 
d ) For example :
TRIM ( ' 2cweb ' ) results : '2 CWEB '
TRIM ( ' S ' FROM ' STEVENS ' ) results : ' TEVEN '
TRIM ( trailing ' S ' FROM ' STEVENS ' ) results : ' STEVEN '
TRIM ( trailing '1 ' from '2 C1 ' ) ) results : '2 C '
TRIM ( Both '1 ' from '123Web111 ' ) results : '23Web '
 

23 . UPPER ( string1 )
a) Function : convert all characters in the string to uppercase .
 
b ) Syntax : upper ( string1 )
 
string1 is the string to convert to uppercase .
 
c ) application version : Oracle 8i , Oracle 9i , Oracle 10g , Oracle 11g
 
d ) For example :
upper ( ' Design and Development ' ) results : ' DESIGN AND DEVELOPMENT '
upper ( ' Home Nguyen 123 ' ) ; Results : ' poinsettia 123 '
 

24 . VSIZE ( expression)
a) Function : returns the number of bytes of an expression.
 
b ) Syntax: VSize ( expression)
 
expression is the string for which to return the number of bytes .
 
c ) application version : Oracle 8i , Oracle 9i , Oracle 10g , Oracle 11g
 
d ) For example :
VSIZE ( ' design and development ' ) Results : 22
VSIZE ( ' design and development ' ) Results : 24
VSIZE ( null ) result : NULL
VSIZE ('' ) result : NULL
VSIZE ( ' ' ) Results : 1
 
Wow , so we go all the functions in group 1 and know, I hope this function will help you to learn the language PL / SQL Oracle .
 
Any comments or questions you please send us your comments using the form below so that we can become more complete than his website .
 
Thank you for visiting my blog , wish you success !

The basic functions in Oracle ( P3 )

The basic functions in Oracle ( P3 ) Posted on 24-03-2014  The basic functions in Oracle ( P3 ) Blog / MSSQL database - oracle - This article is a sequel of the song " The basic functions in Oracle ( P2 ) " . In this section alone will solve all the functions and character strings in group 1 . Are you ready yet , let's get started. This article is a sequel of the song " The basic functions in Oracle ( P2 ) " . In this section alone will solve all the functions and character strings in group 1 . Are you ready yet , let's get started. 5/10 754

Comment:

To comment you must be logged in members.

Files with category

 
File suggestion for you
File top downloads
Codetitle.net - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.net Develope by Vinagon .Ltd