PL/SQL – String Manipulation

String/Character Functions – translate

The Oracle/PLSQL function translate replaces a character string in a string with another specified character string. It replaces only one character at a time.

Syntax:

TRANSLATE( string, from_string, replace_string )

string is the string that has to be searched. from_string is the string that will be searched for in string. replace_string is the set of characters that are used for replacing in the string.

Example code:

SELECT TRANSLATE('what is this happening', 'ins', '1N2') word
FROM dual

word
———————————
what 12 th12 happeN1Ng

SELECT TRANSLATE('12 and 35 adds up to 46', '123456', 'ABCDEF') word
FROM dual

word
———————–
AB and CE adds up to DF

String/Character Functions – rtrim

The Oracle/PLSQL function rtrim removes all specified characters from the right of a string. It removes every occurrence of specified characters that are leading the string.

Syntax:

RTRIM(string, trim_string)

string is the string from which characters have to be trimmed. trim_string is optional. It is the set of characters that will be removed from the right of the string. The default for this is space.

Example code:

SELECT RTRIM('   author   ') trimmed_name
FROM dual

This returns ‘ author‘

SELECT RTRIM('ABCABCauthorABC', 'ABC') trimmed_name
FROM dual

trimmed_name
————-
ABCABCauthor

SELECT RTRIM('124523124author3438', '0123456789') trimmed_name
FROM dual

trimmed_name
————-
124523124author

Note: rtrim removes individual occurrences of the number that are trailing in the string

String/Character Functions – ltrim

The Oracle/PLSQL function ltrim removes all specified characters from the left of a string. It removes every occurrence of specified characters that are leading the string.

Syntax:

LTRIM(string, trim_string)

string is the string from which characters have to be trimmed. trim_string is optional. It is the set of characters that will be removed from the left of the string. The default for this is space.

Example code:

SELECT LTRIM('   author   ') trimmed_name
FROM dual

This returns ‘author ‘

SELECT LTRIM('ABCABCauthorABC', 'ABC') trimmed_name
FROM dual

trimmed_name
————-
authorABC

SELECT LTRIM('124523124author3438', '0123456789') trimmed_name
FROM dual

trimmed_name
————-
author3438

Note: ltrim removes individual occurrences of the number that are leading in the string

String/Character Functions – trim

The Oracle/PLSQL function trim removes the specified characters either sides of a string. Trim can be used to remove spaces(or characters) that are leading, trailing or both from the string by using the keywords trailing, leading or both. By default, trim removes from both front and end of the string.

Syntax:

TRIM(string)

Example code:

SELECT TRIM('    trust  ') trimmed_name
FROM dual

trimmed_name
—————–
trust

SELECT TRIM(' ' FROM '  trust  ') trimmed_name
FROM dual

trimmed_name
—————–
trust

SELECT TRIM(leading 'A' FROM 'AAtrustAAA') trimmed_name
FROM dual

trimmed_name
—————–
trustAAA

SELECT TRIM(trailing 'A' FROM 'AAtrustAAA') trimmed_name
FROM dual

trimmed_name
—————–
AAtrust

SELECT TRIM(both 'A' FROM 'AAtrustAAA') trimmed_name
FROM dual

trimmed_name
—————–
trust

String/Character Functions – concat

The Oracle/PLSQL function concat allows you to concatenate two strings together.

Syntax:

CONCAT(string1, string2)

string1 and string2 are to two strings to be concatenated.

Example code:

SELECT CONCAT('Mad', 'Man')
  FROM dual

name
——-
MadMan

SELECT * FROM books

SELECT book_id, CONCAT(author_id, author_name) author
  FROM books

String/Character Functions – rpad

The Oracle/PLSQL function rpad pads the right-side of a string to a specified length with the specified characters.

Syntax:

RPAD(string, pad_length, pad_string)

string is the string which has to be padded. pad_length is the return value. If the pad_length is smaller than the string, then the string will be truncated to the size of pad_length. pad_string is optional. This is the set of characters that will be padded to the right of string. The default for this is space.

Example code:

SELECT RPAD('Wearhouse', 20) name
FROM dual

name
—————
Wearhouse ’

SELECT RPAD('Wearhouse', 3) name
FROM dual

name
———-
Wea

SELECT RPAD('Wearhouse', 15, 'A') name
FROM dual

name
—————
WearhouseAAAAAA

SELECT RPAD('Wearhouse', 9, 'A') name
FROM dual

name
—————
Wearhouse

String/Character Functions – lpad

The Oracle/PLSQL function lpad pads the left-side of a string to a specified length with the specified characters.

Syntax:

LPAD(string, pad_length, pad_string)

Example code:

SELECT LPAD('Wearhouse', 20) name
FROM dual

name
—————————————-
‘ Wearhouse’

SELECT LPAD('Wearhouse', 20) name
FROM dual

name
———-
Wea

SELECT LPAD('Wearhouse', 15, 'A') name
FROM dual

name
————————-
AAAAAAWearhouse

SELECT LPAD('Wearhouse', 9, 'A') name
FROM dual

name
————————–
Wearhouse

String/Character Functions – upper

The Oracle/PLSQL function upper converts all letters in the specified string to uppercase.

Syntax:

UPPER(string)

Example code:

SELECT UPPER('Tomorrow never dies') name
  FROM dual

name
———————————–
TOMORROW NEVER DIES

String/Character Functions – lower

The Oracle/PLSQL function lower converts all letters in the specified string to lowercase.

Syntax:

LOWER(string)

Example code:

SELECT LOWER('TOMORROW NEVER DIES') name
  FROM dual

name
—————————-
tomorrow never dies

String/Character Functions – length

The Oracle/PLSQL function length returns an integer that is the length of the specified string.

Syntax:

LENGTH(string)

string is the character string whose length is returned. The function returns NULL if the string is null.

Example code:

SELECT LENGTH(NULL) LENGTH
  FROM DUAL

Length
——
null

SELECT LENGTH('Back to the future') LENGTH
  FROM dual

Length
———
18

String/Character Functions – replace()

REPLACE SQL function is used to search for a pattern of characters and change all the instances of that pattern. It returns a string in which all occurrences of match_string in string are replaced by replace_string. If replace_string is not specified, then it removes all occurrences of the match_string.

Syntax:

REPLACE(string, match_string, replace_string)

Example code:

SELECT * FROM books

Book_id	author_id	author_name
11	21	Wordsworth
12	22	Steven
13	23	John
14	24	Steven

SELECT REPLACE(author_name, 'Steven', 'Stephen')
FROM books
WHERE author_id = 22

This replaces all occurrences of Steven with Stephen where the author_id is 22

Book_id	author_id	author_name
11	21	Wordsworth
12	22	Stephen
13	23	John
14	24	Steven

String/Character Functions – initcap()

The Oracle/PLSQL initcap function returns the character string with the first letter in each word in uppercase and rest of the letters in lowercase. It capitalizes only the first character of each word.

Syntax:

INITCAP( string_expr )

Example code:

SELECT INITCAP('this is a project')
FROM dual

This returns This Is A Project

SELECT INITCAP('ALL UPPERCASE TO INITCAP')
FROM dual

This returns All Uppercase To Initcap

String/Character Functions – instr()

The Oracle/PLSQL function instr returns an integer which specifies the location of a substring in a string. In the syntax, the start_position and occurrence are optional. The parameter string is the string that is searched. The search_string is the string or character to search for in the string. The start_position is the position at which the search will start. The default for this argument is 1. The occurrence is the number of occurrence of the search_string. The default for this argument is also 1.

Syntax:

INSTR(string, search_string, start_position, occurrence)

Example code:

SELECT INSTR('Here is my heart', 'e', 1, 3)
FROM dual

This returns 13 (third occurrence of ‘e’)

SELECT INSTR('Here is my heart', 'e', -3, 2)
FROM dual

This returns 4 (negative value in start_position searches from the end of the string)

String/Character Functions – substr()

The Oracle/PLSQL function substr returns a substring from a string. In the syntax below, string is the source string. start_position is the position from where the substring has to be extracted. If start_position is negative, then substr starts from the end of the string and counts backwards. substring_length is optional. It is the number of characters to extract.

Syntax:

SUBSTR(string, start_position, substring_length)

Example code:

SELECT SUBSTR('There is always tomorrow', 10)
FROM dual

This returns ‘always tomorrow’

SELECT SUBSTR('There is always tomorrow', 10, 6)
FROM dual

This returns ‘always’

SELECT SUBSTR('There is always tomorrow', -3, 3)
FROM dual

This returns row