ORACLE SUBSTR(STRING/COLUMN NAME, START POSITION, UP TO WHEN I.E. LIMIT)
SUBSTR function i.e. Sub-String function is always used to cut the piece of string from your given either string or column name value. Below are some examples to make you clear your concepts
select substr('Tech on the net set met pet', '1') from dual; --- Tech on the net set met pet
select substr('Tech on the net set met pet', '1','0') from dual; --- Return Nothing
select substr('Tech on the net set met pet', '1','1') from dual; --- T
select substr('Tech on the net set met pet', '1','2') from dual; --- Te
select substr('Tech on the net set met pet','1','3') from dual; --- Tec
select substr('Tech on the net set met pet','1','4') from dual; --- Tech
select substr('Tech on the net set met pet', '2') from dual; --- ech on the net set met pet
select substr('Tech on the net set met pet', '2','0') from dual; --- Return Nothing
select substr('Tech on the net set met pet', '2','1') from dual; --- e
select substr('Tech on the net set met pet', '2','2') from dual; --- ec
select substr('Tech on the net set met pet','2','3') from dual; --- ech
select substr('Tech on the net set met pet','2','5') from dual; --- ech o
Kindly follow the color matching pattern. In the above examples it is clearly shown that from the start position till it's end position, function is returning the outputs. If we are not mentioning the end position then it will return the complete string.
String Name (Or Any Column Name) I.E. 'Tech on the net set met pet',
A Starting Position I.E. '2' from LEFT TO RIGHT,
Limit Up To When Function Will Cut The String I.E. '5' from LEFT TO RIGHT.
But it is little different in it's REVERSE counts
select substr('Tech on the net set met pet', '-1') from dual; --- t
select substr('Tech on the net set met pet', '-1','0') from dual; --- Return Nothing
select substr('Tech on the net set met pet', '-1','1') from dual; --- t
select substr('Tech on the net set met pet', '-1','2') from dual; --- t
select substr('Tech on the net set met pet','-1','3') from dual; --- t
select substr('Tech on the net set met pet','-1','4') from dual; --- t
select substr('Tech on the net set met pet', '-2') from dual; --- et
select substr('Tech on the net set met pet', '-2','0') from dual; --- Return Nothing
select substr('Tech on the net set met pet', '-2','1') from dual; --- e
select substr('Tech on the net set met pet', '-2','2') from dual; --- et
select substr('Tech on the net set met pet','-2','3') from dual; --- et
select substr('Tech on the net set met pet','-2','4') from dual; --- et
select substr('Tech on the net set met pet', '-3','1') from dual; --- p
select substr('Tech on the net set met pet', '-3','2') from dual; --- pe
select substr('Tech on the net set met pet','-3','3') from dual; --- pet
select substr('Tech on the net set met pet','-3','4') from dual; --- pet
select substr('Tech on the net set met pet', '-5','1') from dual; --- t
select substr('Tech on the net set met pet', '-5','2') from dual; --- t(with space)
select substr('Tech on the net set met pet','-5','3') from dual; --- t p
select substr('Tech on the net set met pet','-5','4') from dual; --- t pe
select substr('Tech on the net set met pet','-5','5') from dual; --- t pet
Here we can easily see that the
Start Position I.E. '-5' is taking the START position from the RIGHT TO LEFT but the
End Position I.E. '5' is taking the END position counts from LEFT TO RIGHT
ORACLE INSTR(STRING/COLUMN NAME, OCCURRENCE STRING/NUMBER/CHARACTER , START POSITION, POSITION OF OCCURRENCE)
INSTR function is use to show the position of your occurrence string (string or column name data or character or number). It gives you the occurrence position starting from which POSITION & up to which POSITION OF OCCURRENCE in the string.
NOTE: THE COUNT WILL ALWAYS START FROM THE BEGINNING POINT OF THE STRING
Like if we say instr('abcde abcd abc ab a', 'a', '2', '3'), it simple means the instr function wants the output that is starting from the 2nd position, search the character 'a' up to it's 3rd position of occurrence & gives the output count start from the beginning, So the output will be 16th position.
NOTE: THE COUNT WILL ALWAYS START FROM THE BEGINNING POINT OF THE STRING
select instr('Tech on the net set met pet', 'e') from dual; --- will return 2 DEFAULT IT WILL TAKE THE 1ST POSITION & 1ST OCCURRENCE OF 'e'
select instr('Tech on the net set met pet', 'e','0') from dual; --- will return 0 NO POSITION SO NO OCCURRENCE
select instr('Tech on the net set met pet', 'e','0','0') from dual; --- will return 0 OUT OF RANGE (OCCURRENCE ALWAYS > 0)
select instr('Tech on the net set met pet', 'e','1') from dual; --- will return 2
select instr('Tech on the net set met pet', 'e','1','1') from dual; --- will return 2
select instr('Tech on the net set met pet', 'e','1','2') from dual; --- will return 11
select instr('Tech on the net set met pet', 'e','1','3') from dual; --- will return 14
select instr('Tech on the net set met pet', 'e','1','4') from dual; --- will return 18
select instr('Tech on the net set met pet', 'e','2','1') from dual; --- will return 2
select instr('Tech on the net set met pet', 'e','2','2') from dual; --- will return 11
select instr('Tech on the net set met pet', 'e','2','3') from dual; --- will return 14
select instr('Tech on the net set met pet', 'e','2','4') from dual; --- will return 18
select instr('Tech on the net set met pet', 'e','3','1') from dual; --- will return 11
select instr('Tech on the net set met pet', 'e','3','2') from dual; --- will return 14
select instr('Tech on the net set met pet', 'e','3','3') from dual; --- will return 18
select instr('Tech on the net set met pet', 'e','3','4') from dual; --- will return 22
Here it simply says,
'e' denotes the searching string,
'3' denotes the third position of the string 'Tech on the net set met pet', i.e. c
'4' denotes the position of occurrence of 'e' after 3rd position (i.e. c) of the string 'Tech on the net set met pet' here count starts from beginning i.e.'T'
NOTE: THE COUNT WILL ALWAYS START FROM THE BEGINNING POINT OF THE STRING
But it is little different in it's REVERSE counts
select instr('Tech on the net set met pet', 'e') from dual; --- will return 2 DEFAULT IT WILL TAKE THE 1ST POSITION & 1ST OCCURRENCE OF 'e'
select instr('Tech on the net set met pet', 'e','-0') from dual; --- will return 0 NO POSITION SO NO OCCURRENCE
select instr('Tech on the net set met pet', 'e','-0','0') from dual; --- will return 0 OUT OF RANGE (OCCURRENCE ALWAYS > 0)
select instr('Tech on the net set met pet', 'e','-1') from dual; --- will return 26 (Reversing)
select instr('Tech on the net set met pet', 'e','-1','1') from dual; --- will return 26
select instr('Tech on the net set met pet', 'e','-1','2') from dual; --- will return 22
select instr('Tech on the net set met pet', 'e','-1','3') from dual; --- will return 18
select instr('Tech on the net set met pet', 'e','-1','4') from dual; --- will return 14
select instr('Tech on the net set met pet', 'e','-2','1') from dual; --- will return 26
select instr('Tech on the net set met pet', 'e','-2','2') from dual; --- will return 22
select instr('Tech on the net set met pet', 'e','-2','3') from dual; --- will return 18
select instr('Tech on the net set met pet', 'e','-2','4') from dual; --- will return 14
select instr('Tech on the net set met pet', 'e','-3','1') from dual; --- will return 22
select instr('Tech on the net set met pet', 'e','-3','2') from dual; --- will return 18
select instr('Tech on the net set met pet', 'e','-3','3') from dual; --- will return 14
select instr('Tech on the net set met pet', 'e','-3','4') from dual; --- will return 11
Here it simply says,
'e' denotes the searching string,
'-3' denotes the third position of the string 'Tech on the net set met pet', i.e. p
'4' denotes the position of occurrence of 'e' after -3rd position (i.e. p) of the string 'Tech on the net set met pet' here count starts from beginning i.e.'T'
NOTE: THE COUNT WILL ALWAYS START FROM THE BEGINNING POINT OF THE STRING
DUMMY EXAMPLE INCLUDED SUBSTR WITH INSTR
SELECT
SUBSTR('Tech <br> on <br> the <br>',1) "String",
/*ELIMINATE THE BR FROM THE START*/
SUBSTR('Tech <br> on <br> the <br>', 1 , INSTR('Tech <br> on <br> the <br>','<br>',1,1) +4) "SHOW FIRST BR1",
SUBSTR('Tech <br> on <br> the <br>', 1 , INSTR('Tech <br> on <br> the <br>','<br>',1,2) +4) "SHOW SECOND BR2",
SUBSTR('Tech <br> on <br> the <br>', 1 , INSTR('Tech <br> on <br> the <br>','<br>',1,3) +4) "SHOW LAST BR3",
/*ELIMINATE THE BR FROM THE LAST*/
SUBSTR('Tech <br> on <br> the <br>', 1 , INSTR('Tech <br> on <br> the <br>','<br>',-1,1) -1) "ELIMINATE LAST BR3",
SUBSTR('Tech <br> on <br> the <br>', 1 , INSTR('Tech <br> on <br> the <br>','<br>',-1,2) -1) "ELIMINATE SECOND BR2",
SUBSTR('Tech <br> on <br> the <br>', 1 , INSTR('Tech <br> on <br> the <br>','<br>',-1,3) -1) "ELIMINATE FIRST BR1"
FROM DUAL;
ORACLE UPPER/ LOWER/ INITCAP/ LENGTH/ CONCAT & REPLACE FUNCTIONS
Below one example will make you understand these functions functionality
ORACLE TRIM/ LTRIM/ RTRIM FUNCTION
Oracle TRIM( [ LEADING | TRAILING | BOTH [ trim_character ] string1 ) function is use to trim the searching string from the beginning(LEADING), ending(TRAILING) or from the both(BOTH) side of the given column or string.
NOTE: THE TRIM_CHARACTER SHOULD BE A SINGLE UNIT I.E. EITHER A, B, C, 1, 2, 3 BUT NOT AB, CD, RAM, KAJU, 123 ETC
Oracle LTRIM function is use to trim the searching string from the LEFT-SIDE of the string & Oracle RTRIM function is use to trim the searching string from the RIGHT-SIDE of the string
ORACLE LPAD / RPAD FUNCTION
Oracle LPAD / RPAD function is used for padding (adding) a set of information from left or right side. The syntax for padding is LPAD/RPAD( string1, padded_length, [ pad_string ] )
NOTE: IF YOU HAVE DOUBTS, KINDLY CONTACT ME VIA THIS BLOG
SUBSTR function i.e. Sub-String function is always used to cut the piece of string from your given either string or column name value. Below are some examples to make you clear your concepts
select substr('Tech on the net set met pet', '1') from dual; --- Tech on the net set met pet
select substr('Tech on the net set met pet', '1','0') from dual; --- Return Nothing
select substr('Tech on the net set met pet', '1','1') from dual; --- T
select substr('Tech on the net set met pet', '1','2') from dual; --- Te
select substr('Tech on the net set met pet','1','3') from dual; --- Tec
select substr('Tech on the net set met pet','1','4') from dual; --- Tech
select substr('Tech on the net set met pet', '2') from dual; --- ech on the net set met pet
select substr('Tech on the net set met pet', '2','0') from dual; --- Return Nothing
select substr('Tech on the net set met pet', '2','1') from dual; --- e
select substr('Tech on the net set met pet', '2','2') from dual; --- ec
select substr('Tech on the net set met pet','2','3') from dual; --- ech
select substr('Tech on the net set met pet','2','5') from dual; --- ech o
Kindly follow the color matching pattern. In the above examples it is clearly shown that from the start position till it's end position, function is returning the outputs. If we are not mentioning the end position then it will return the complete string.
String Name (Or Any Column Name) I.E. 'Tech on the net set met pet',
A Starting Position I.E. '2' from LEFT TO RIGHT,
Limit Up To When Function Will Cut The String I.E. '5' from LEFT TO RIGHT.
But it is little different in it's REVERSE counts
select substr('Tech on the net set met pet', '-1') from dual; --- t
select substr('Tech on the net set met pet', '-1','0') from dual; --- Return Nothing
select substr('Tech on the net set met pet', '-1','1') from dual; --- t
select substr('Tech on the net set met pet', '-1','2') from dual; --- t
select substr('Tech on the net set met pet','-1','3') from dual; --- t
select substr('Tech on the net set met pet','-1','4') from dual; --- t
select substr('Tech on the net set met pet', '-2') from dual; --- et
select substr('Tech on the net set met pet', '-2','0') from dual; --- Return Nothing
select substr('Tech on the net set met pet', '-2','1') from dual; --- e
select substr('Tech on the net set met pet', '-2','2') from dual; --- et
select substr('Tech on the net set met pet','-2','3') from dual; --- et
select substr('Tech on the net set met pet','-2','4') from dual; --- et
select substr('Tech on the net set met pet', '-3','1') from dual; --- p
select substr('Tech on the net set met pet', '-3','2') from dual; --- pe
select substr('Tech on the net set met pet','-3','3') from dual; --- pet
select substr('Tech on the net set met pet','-3','4') from dual; --- pet
select substr('Tech on the net set met pet', '-5','1') from dual; --- t
select substr('Tech on the net set met pet', '-5','2') from dual; --- t(with space)
select substr('Tech on the net set met pet','-5','3') from dual; --- t p
select substr('Tech on the net set met pet','-5','4') from dual; --- t pe
select substr('Tech on the net set met pet','-5','5') from dual; --- t pet
Here we can easily see that the
Start Position I.E. '-5' is taking the START position from the RIGHT TO LEFT but the
End Position I.E. '5' is taking the END position counts from LEFT TO RIGHT
ORACLE INSTR(STRING/COLUMN NAME, OCCURRENCE STRING/NUMBER/CHARACTER , START POSITION, POSITION OF OCCURRENCE)
INSTR function is use to show the position of your occurrence string (string or column name data or character or number). It gives you the occurrence position starting from which POSITION & up to which POSITION OF OCCURRENCE in the string.
NOTE: THE COUNT WILL ALWAYS START FROM THE BEGINNING POINT OF THE STRING
Like if we say instr('abcde abcd abc ab a', 'a', '2', '3'), it simple means the instr function wants the output that is starting from the 2nd position, search the character 'a' up to it's 3rd position of occurrence & gives the output count start from the beginning, So the output will be 16th position.
NOTE: THE COUNT WILL ALWAYS START FROM THE BEGINNING POINT OF THE STRING
select instr('Tech on the net set met pet', 'e') from dual; --- will return 2 DEFAULT IT WILL TAKE THE 1ST POSITION & 1ST OCCURRENCE OF 'e'
select instr('Tech on the net set met pet', 'e','0') from dual; --- will return 0 NO POSITION SO NO OCCURRENCE
select instr('Tech on the net set met pet', 'e','0','0') from dual; --- will return 0 OUT OF RANGE (OCCURRENCE ALWAYS > 0)
select instr('Tech on the net set met pet', 'e','1') from dual; --- will return 2
select instr('Tech on the net set met pet', 'e','1','1') from dual; --- will return 2
select instr('Tech on the net set met pet', 'e','1','2') from dual; --- will return 11
select instr('Tech on the net set met pet', 'e','1','3') from dual; --- will return 14
select instr('Tech on the net set met pet', 'e','1','4') from dual; --- will return 18
select instr('Tech on the net set met pet', 'e','2','1') from dual; --- will return 2
select instr('Tech on the net set met pet', 'e','2','2') from dual; --- will return 11
select instr('Tech on the net set met pet', 'e','2','3') from dual; --- will return 14
select instr('Tech on the net set met pet', 'e','2','4') from dual; --- will return 18
select instr('Tech on the net set met pet', 'e','3','1') from dual; --- will return 11
select instr('Tech on the net set met pet', 'e','3','2') from dual; --- will return 14
select instr('Tech on the net set met pet', 'e','3','3') from dual; --- will return 18
select instr('Tech on the net set met pet', 'e','3','4') from dual; --- will return 22
Here it simply says,
'e' denotes the searching string,
'3' denotes the third position of the string 'Tech on the net set met pet', i.e. c
'4' denotes the position of occurrence of 'e' after 3rd position (i.e. c) of the string 'Tech on the net set met pet' here count starts from beginning i.e.'T'
NOTE: THE COUNT WILL ALWAYS START FROM THE BEGINNING POINT OF THE STRING
But it is little different in it's REVERSE counts
select instr('Tech on the net set met pet', 'e') from dual; --- will return 2 DEFAULT IT WILL TAKE THE 1ST POSITION & 1ST OCCURRENCE OF 'e'
select instr('Tech on the net set met pet', 'e','-0') from dual; --- will return 0 NO POSITION SO NO OCCURRENCE
select instr('Tech on the net set met pet', 'e','-0','0') from dual; --- will return 0 OUT OF RANGE (OCCURRENCE ALWAYS > 0)
select instr('Tech on the net set met pet', 'e','-1') from dual; --- will return 26 (Reversing)
select instr('Tech on the net set met pet', 'e','-1','1') from dual; --- will return 26
select instr('Tech on the net set met pet', 'e','-1','2') from dual; --- will return 22
select instr('Tech on the net set met pet', 'e','-1','3') from dual; --- will return 18
select instr('Tech on the net set met pet', 'e','-1','4') from dual; --- will return 14
select instr('Tech on the net set met pet', 'e','-2','1') from dual; --- will return 26
select instr('Tech on the net set met pet', 'e','-2','2') from dual; --- will return 22
select instr('Tech on the net set met pet', 'e','-2','3') from dual; --- will return 18
select instr('Tech on the net set met pet', 'e','-2','4') from dual; --- will return 14
select instr('Tech on the net set met pet', 'e','-3','1') from dual; --- will return 22
select instr('Tech on the net set met pet', 'e','-3','2') from dual; --- will return 18
select instr('Tech on the net set met pet', 'e','-3','3') from dual; --- will return 14
select instr('Tech on the net set met pet', 'e','-3','4') from dual; --- will return 11
Here it simply says,
'e' denotes the searching string,
'-3' denotes the third position of the string 'Tech on the net set met pet', i.e. p
'4' denotes the position of occurrence of 'e' after -3rd position (i.e. p) of the string 'Tech on the net set met pet' here count starts from beginning i.e.'T'
NOTE: THE COUNT WILL ALWAYS START FROM THE BEGINNING POINT OF THE STRING
DUMMY EXAMPLE INCLUDED SUBSTR WITH INSTR
SELECT
SUBSTR('Tech <br> on <br> the <br>',1) "String",
/*ELIMINATE THE BR FROM THE START*/
SUBSTR('Tech <br> on <br> the <br>', 1 , INSTR('Tech <br> on <br> the <br>','<br>',1,1) +4) "SHOW FIRST BR1",
SUBSTR('Tech <br> on <br> the <br>', 1 , INSTR('Tech <br> on <br> the <br>','<br>',1,2) +4) "SHOW SECOND BR2",
SUBSTR('Tech <br> on <br> the <br>', 1 , INSTR('Tech <br> on <br> the <br>','<br>',1,3) +4) "SHOW LAST BR3",
/*ELIMINATE THE BR FROM THE LAST*/
SUBSTR('Tech <br> on <br> the <br>', 1 , INSTR('Tech <br> on <br> the <br>','<br>',-1,1) -1) "ELIMINATE LAST BR3",
SUBSTR('Tech <br> on <br> the <br>', 1 , INSTR('Tech <br> on <br> the <br>','<br>',-1,2) -1) "ELIMINATE SECOND BR2",
SUBSTR('Tech <br> on <br> the <br>', 1 , INSTR('Tech <br> on <br> the <br>','<br>',-1,3) -1) "ELIMINATE FIRST BR1"
FROM DUAL;
ORACLE UPPER/ LOWER/ INITCAP/ LENGTH/ CONCAT & REPLACE FUNCTIONS
Below one example will make you understand these functions functionality
ORACLE TRIM/ LTRIM/ RTRIM FUNCTION
Oracle TRIM( [ LEADING | TRAILING | BOTH [ trim_character ] string1 ) function is use to trim the searching string from the beginning(LEADING), ending(TRAILING) or from the both(BOTH) side of the given column or string.
NOTE: THE TRIM_CHARACTER SHOULD BE A SINGLE UNIT I.E. EITHER A, B, C, 1, 2, 3 BUT NOT AB, CD, RAM, KAJU, 123 ETC
Oracle LTRIM function is use to trim the searching string from the LEFT-SIDE of the string & Oracle RTRIM function is use to trim the searching string from the RIGHT-SIDE of the string
ORACLE LPAD / RPAD FUNCTION
Oracle LPAD / RPAD function is used for padding (adding) a set of information from left or right side. The syntax for padding is LPAD/RPAD( string1, padded_length, [ pad_string ] )
NOTE: IF YOU HAVE DOUBTS, KINDLY CONTACT ME VIA THIS BLOG
No comments:
Post a Comment