The following are some of the ways strings can be manipulated in the SQL command.

• Wild card character for pattern matching: _ and % that can be used in the WHERE clause. The underline '_' represents one space or one of any character and % = any number of characters

 

 
 
 
 
 

Note that MS Access uses '?' for 1 character and '*' for any number of characters.

SELECT  EmployeeName 
FROM    Employee
WHERE   EmployeeName LIKE '_ash%';

will select any employee name starting with one of any character with 'ash' and following by any characters (i.e, Nash, Washington, etc.)

• String concatenation || can be use in the SELECT clause

SELECT  area_code || '-' || phone_number 
FROM    address;

will concate the area code 518 and phone number 2766666 and produce a result like 518-2766666.

• Search strings’ position

INSTR(string, set [,start, [occurrence]])

• Substring

SUBSTR(string, start, count)

Exit to : Oracle Hints and Tips