String Operations
To compare an attribute with a string, it is required to surround the string by apostrophes, e.g., where LOCATION = ’HARIDWAR’.
Wild cards (LIKE operator)
There are two wildcards used in conjunction with the LIKE operator:
% - The percent sign represents zero, one, or multiple characters
_ - The underscore represents a single character
For example, if one is interested in all tuples of the table DEPT that contain two C in the name of the department, the condition would be where DNAME like ’%C%C%’.
The underline stands for exactly one character. Thus the condition where DNAME like ’%C C%’ would require that exactly one character appears between the two Cs.
More string operations are:
• upper(<string>) takes a string and converts any letters in it to uppercase,
e.g., DNAME= upper(DNAME) (The name of a department must consist only of upper case letters.)
• lower(<string>) converts any letter to lowercase,
• initcap(<string>) converts the initial letter of every word in <string> to uppercase.
• length(<string>) returns the length of the string.
• substr(<string>, n [, m]) clips out a m character piece of <string>, starting at position n. If m is not specified, the end of the string is assumed.
SELECT SUBSTR('Vertabelo Academy',11,7) FROM DUAL; -- Academy
CONCAT(first_char, second_char, ... n_char) combines two or more strings into one string
SELECT CONCAT ('Vertabelo Academy is good', ' and great', ' and fantastic!') FROM DUAL;
CONCAT can be replaced by the string concatenation symbol “||”
REPLACE REPLACE allows you to selectively replace or remove data from inside a string
SELECT REPLACE (‘IRIS Software is good!', 'good', 'great!') FROM DUAL; -- IRIS Software is great
TRIM Its main job is removing all specified characters from the beginning part (leading),ending part (trailing), or both parts (both) of a specific string (edit_char).
TRIM( [ [ LEADING | TRAILING | BOTH ] character FROM ] edit_char )
SELECT TRIM(‘ Academy2017 ') FROM DUAL; -- Academy2017
SELECT TRIM (BOTH '20' FROM '2017VertabeloAcademy20') FROM DUAL; -- 17VertabeloAcademy
INSTR returns the location of a substring in a string
INSTR( string, substring [, start_position [, th_appearance ] ] )
INSTR('Tech on the net', 'e')
Result: 2 (the first occurrence of 'e')
INSTR('Tech on the net', 'e', 1, 1)
Result: 2 (the first occurrence of 'e')
INSTR('Tech on the net', 'e', 1, 2)
Result: 11 (the second occurrence of 'e')
INSTR('Tech on the net', 'e', 1, 3)
Result: 14 (the third occurrence of 'e')
INSTR('Tech on the net', 'e', -3, 2)
Result: 2
COALESCE Function Allows you to return the first non-NULL value from a list of parameters
COALESCE ( expr1, expr2, [expr...] )
The downside is that it only transforms NULL values. It can't change other values, such as 0, or advanced logic;
select coalesce (valye1,value2,value3,'Y') from
(select NULL as value1 Null asvalue2,'3' as value3 from duel);
DECODE Function Allows you to have IF-THEN-ELSE logic in your SQL statements.
DECODE ( expression, search, result [, search, result]... [,default] )
