Character Functions

Character Manipulator Functions

Lower	Upper	InitCap	Trim	Substr	Concat	Replace	Soundex	Length

LOWER(string1)

• Converts string1 to lower case

Examples

When wanting results to show as lower

-- Assume Chad and Josh exist in the table
SELECT LOWER(First_Name) as Names
From Student.Student

Outputs:

Names
josh
chad

When Searching for Values that meet Criteria

SELECT Last_Name
FROM Student.Student
WHERE LOWER('Frank') = Lower(First_Name)

Outputs:

First_Name
Frank
Frank
Frank

---

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

Select the third through sixth values of Last_Name

SELECT Last_Name, SUBSTR(Last_Name, 3, 4)
FROM Student.Student;

Output:

Last_Name	SUBSTR(Last_Name, 3, 4)
Jensen	nsen
Kennedy	nned

Get the first characters of first and last name

SELECT First_Name || ' ' || Last_Name as Full_Name, 
              SUBSTR(First_Name, 1, 1) || SUBSTR(Last_Name, 1,1) as Initials
FROM Student.Student;

Output:

FULL_NAME	Initials
Josh Kennedy	JK
Chad Jensen	CJ

---

CONCAT(string1, string2)

• Concatenates two strings.
• The || is an extension of the Concat function. * For Example: First_Name || Last_Name = CONCAT(First_Name,Last_Name) * For the purpose of readability, use || .

-- BAD CODE. DO NOT USE THIS
SELECT CONCAT(First_Name, CONCAT(' ', Last_Name))
FROM Student.Student

-- THIS IMPLEMENTATION IS BETTER
SELECT First_NAME || ' ' || Last_Name as FULL_NAME
FROM Student.Student

Output:

FULL_NAME
Chad Jensen
Josh Kenedy

###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 Diana
SELECT First_Name
FROM Student.Student
WHERE SOUNDEX(First_Name) = SOUNDEX('Diana');

FIRST_NAME
Diana
Dihanna
Dyanah

---

LENGTH(string)

• Counts the number of chars in a string

Examples

Count number of characters of string

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

Output:

FIRST_NAME	LENGTH(Last_Name)
Josh	4
Chad	4
Frank	5

SELECT First_Name
FROM Student.Student
WHERE LENGTH(First_Name) = 4;

Output:

FIRST_NAME
Josh
Chad

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