Character Functions

| Lower | | Upper | | InitCap | | Trim | | Substr | | Concat | | Replace| | Soundex | | Length |

LOWER(string1)

*Converts string1 to lower case

Examples

-- When Comparing values, must use lower in the WHERE 
SELECT *
FROM Student.myTable
WHERE LOWER(inputValue) = DBvalue

---

UPPER(string1)

*Converts string1 to Upper Case

Examples ===

-- Assume for this example that Lawrence and Wichita exist
SELECT UPPER(CITY)
from Student.Zipcode;	

Output:

UPPER(CITY)
LAWRENCE
WICHITA

---

INITCAP(string1)

*Capitalizes first letter of string1

Examples

-- Assume for this example that lawrence and wichita exist
SELECT INITCAP(CITY)
from Student.Zipcode;	

Output: | UPPER(CITY) | | Lawrence | | Wichita |

---

TRIM([LEADING|TRAILING|BOTH] trim_character FROM string1)

• Trims ALL leading / trailing / both sides of a string of character(s)

Examples

//Trim leading zeroes from 006610://

SELECT TRIM(LEADING '0' FROM Zip) as zip
FROM Student.Zipcode;
-- 

Output: | Zip | | 6610 |

//Trim Trailing zeroes from 066100://

SELECT TRIM(TRAILING '0' FROM '066100') as Zip
FROM Dual;

Output: | Zip | | 0661 |

//Trim Both Leading, and Trailing 0's from 066100//

SELECT TRIM(BOTH '0' FROM '066100') as Zip
FROM Dual;

Output: | Zip | | 661 |

---

SUBSTR(string1, start_position, [length])

• Cuts out a piece of a string and returns the cut

Examples

-- Returns the third through fifth values of the Last_Name
SELECT Last_Name, SUBSTR(Last_Name, 3, 3)
FROM Student.Student;

---

CONCAT(string1, string2)

• Concatenates two strings.

Examples

---

REPLACE(string1, string_to_replace, [replacement_string])

• Replaces a string with another string

Examples

-- Replace the salutation of Mr. with Sir --
SELECT REPLACE(Salutation, 'Mr.', 'Sir') as Salutation
FROM Student.Student;

---

SOUNDEX(string1)

*Returns Phonetic representation of string

Examples

-- Sounds like Martin
SELECT Last_Name
FROM Student.Student
Where SOUNDEX(Last_Name) = Soundex('Martin');

---

LENGTH(string)

• Counts the number of chars in a string

Examples

SELECT Last_Name, LENGTH(Last_Name)
FROM Student.Student;

++++++++++++++++++++++++++++++++++

last part