String Operations

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] )