Oracle String functions

The Oracle/PLSQL ASCII function returns the NUMBER code that represents the specified character.

Syntax:

ASCII( p_character )

Arguments:

p_character

• The specified character to retrieve the NUMBER code for. If more than one character is entered, the ASCII function will return the value for the first character and ignore all of the characters after the first.

See also ASCII table:

Dec	Hex	Oct	Char	Description		Dec	Hex	Oct	Char	Description
0	0	0		null		64	40	100	@
1	1	1		start of heading		65	41	101	A
2	2	2		start of text		66	42	102	B
3	3	3		end of text		67	43	103	C
4	4	4		end of transmission		68	44	104	D
5	5	5		enquiry		69	45	105	E
6	6	6		acknowledge		70	46	106	F
7	7	7		bell		71	47	107	G
8	8	10		backspace		72	48	110	H
9	9	11		horizontal tab		73	49	111	I
10	A	12		new line		74	4A	112	J
11	B	13		vertical tab		75	4B	113	K
12	C	14		new page		76	4C	114	L
13	D	15		carriage return		77	4D	115	M
14	E	16		shift out		78	4E	116	N
15	F	17		shift in		79	4F	117	O
16	10	20		data link escape		80	50	120	P
17	11	21		device control 1		81	51	121	Q
18	12	22		device control 2		82	52	122	R
19	13	23		device control 3		83	53	123	S
20	14	24		device control 4		84	54	124	T
21	15	25		negative acknowledge		85	55	125	U
22	16	26		synchronous idle		86	56	126	V
23	17	27		end of trans. block		87	57	127	W
24	18	30		cancel		88	58	130	X
25	19	31		end of medium		89	59	131	Y
26	1A	32		substitute		90	5A	132	Z
27	1B	33		escape		91	5B	133	[
28	1C	34		file separator		92	5C	134	\
29	1D	35		group separator		93	5D	135	]
30	1E	36		record separator		94	5E	136	^
31	1F	37		unit separator		95	5F	137	_
32	20	40		space		96	60	140	`
33	21	41	!			97	61	141	a
34	22	42	"			98	62	142	b
35	23	43	#			99	63	143	c
36	24	44	$			100	64	144	d
37	25	45	%			101	65	145	e
38	26	46	&			102	66	146	f
39	27	47	'			103	67	147	g
40	28	50	(			104	68	150	h
41	29	51	)			105	69	151	i
42	2A	52	*			106	6A	152	j
43	2B	53	+			107	6B	153	k
44	2C	54	,			108	6C	154	l
45	2D	55	-			109	6D	155	m
46	2E	56	.			110	6E	156	n
47	2F	57	/			111	6F	157	o
48	30	60	0			112	70	160	p
49	31	61	1			113	71	161	q
50	32	62	2			114	72	162	r
51	33	63	3			115	73	163	s
52	34	64	4			116	74	164	t
53	35	65	5			117	75	165	u
54	36	66	6			118	76	166	v
55	37	67	7			119	77	167	w
56	38	70	8			120	78	170	x
57	39	71	9			121	79	171	y
58	3A	72	:			122	7A	172	z
59	3B	73	;			123	7B	173	{
60	3C	74	<			124	7C	174	|
61	3D	75	=			125	7D	175	}
62	3E	76	>			126	7E	176	~
63	3F	77	?		127	7F	177	DEL

The Oracle COALESCE function returns the first non-NULL expression in the list. If all expressions in the list evaluate to NULL, then the COALESCE function will return NULL. The Oracle COALESCE function makes use of "short-circuit evaluation". The database evaluates each expression's value and determines whether it is NULL, rather than evaluating all of the expressions before determining if any of them are NULL.

Syntax:

COALESCE( p_expression1, p_expression2, ... p_expressionN )

Arguments:

• p_expression1, p_expression2, .. p_expressionN

• The expressions to test for non-null values.

Example:

-- --> 'Abc'
Select COALESCE(null, 'Abc', '123') as Column1 from dual;


-- --> 'Aaa'
Select COALESCE('Aaa', null, '345') as Column1 from dual;


-- --> 'Sss'
Select COALESCE(null, null, null, 'Sss') as Column1 from dual;

UPPER function converts all letters in the specified string to uppercase.

Syntax:

UPPER( p_string )

Arguments:

p_string

• The string to convert to uppercase.

Example:

-- --> TOM CAT
Select Upper('Tom Cat') From Dual;

-- --> TOM CAT
Select Upper('tom cat') From Dual;

The LOWER function converts all letters in the specified string to lowercase.

Syntax:

LOWER( p_string )

Arguments:

p_string

p_string

• The string to convert to lowercase.

Example:

-- --> tom cat
Select Lower('Tom Cat') From Dual;

-- --> tom cat
Select Lower('tom cat') From Dual;

The Oracle INITCAP function returns a character string with the first letter of each word converted to uppercase (also called "title case"). All other letters in the string will be converted to lowercase. Words are delimited by white space (spaces, tabs, carriage returns, formfeeds, vertical-tabs, newlines) or characters that are not alphanumeric.

Syntax:

INITCAP(  p_string )

Argument:

p_string

• The string argument whose first character in each word will be converted to uppercase and all remaining characters converted to lowercase.

Example:

-- --> Tom Cat
Select Initcap('TOM CAT') From Dual;

-- --> Tom Cat
Select Initcap('tom cat') From Dual;

The Oracle CHR() function returns the ASCII character that corresponds to the value passed to it. If using NCHAR_CS is specified, it returns the character that corresponds to the national character set.

Syntax:

CHR( p_number )

CHR( p_number  using nchar_cs)
 

Arguments:

p_number

• The NUMBER code used to retrieve the character.

Example:

-- --> @
Select  chr(64) From dual;

-- --> A
Select  chr(65) From dual;

-- --> â
Select  chr(50082) From dual;

-- --> 쎢
Select  chr(50082  using nchar_cs) From dual;

The CONCAT function allows you to concatenate two strings together.

Syntax:

CONCAT( p_string1, p_string2 )

Arguments:

p_string1

• The first string to concatenate.

p_string2

• The second string to concatenate.

Example:

-- --> Tom Cat
Select Concat('Tom ', 'Cat') From dual;

The CONCAT function allows you to concatenate two strings together.

Syntax:

CONCAT( p_string1, p_string2 )

• CONCAT(CLOB, NCLOB) returns NCLOB
• CONCAT(NCLOB, NCHAR) returns NCLOB
• CONCAT(NCLOB, CHAR) returns NCLOB
• CONCAT(NCHAR, CLOB) returns NCLOB

Arguments:

p_string1

• The first string to concatenate.

p_string2

• The second string to concatenate.

Example:

Declare
   C1 Clob := To_Clob('Tom ');
   C2 Clob := To_Clob('Cat');
   C3 Clob;
   C4 Clob;
Begin
   Select Concat(C1
                ,C2)
   Into   C3
   From   Dual;
   ----
   -- --> Tom Cat
   Dbms_Output.Put_Line(C3);
   ----
   C4 := Concat(C1
               ,C2);
   -- --> Tom Cat
   Dbms_Output.Put_Line(C4);
End;

The INSTR function returns the location of a substring in a string.

Syntax:

INSTR( p_string, p_substring [, p_start_position [,  p_occurrence ] ] )

p_string

• The string to search. p_string can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

p_substring

• The substring to search for in p_string. p_substring can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

p_start_position

• Optional. The position in p_string where the search will start. If omitted, it defaults to 1. The first position in the string is 1. If the p_start_position is negative, the INSTR function counts back p_start_position number of characters from the end of string and then searches towards the beginning of string.

p_occurrence

• Optional. The nth appearance of p_substring. If omitted, it defaults to 1.

Example:

-- --> 14
Select Instr('Corporate Floor', 'or', 5, 2) as Column1 from dual;


-- --> 5
Select Instr('Corporate Floor', 'or', -10) as Column1 from dual;

See also:

• REGEXP_INSTR

The INSTRB function returns the location of a substring in a string, using bytes instead of characters.

Syntax:

INSTRB (p_string , p_substring [, p_start_position [, p_occurrence]])

The INSTRB function returns the location of a p_substring in a p_string, using bytes instead of characters.

Arguments:

p_string

• The text expression to search.

p_substring

•  The string to search for.

p_start_position

• A nonzero INTEGER indicating the byte of string where the function begins the search. When p_start_position is negative, then INSTRB counts and searches backward from the end of string. The default value of p_start_position is 1, which means that the function begins searching at the first byte of string.

p_occurrence

• An INTEGER indicating which occurrence of string the function should search for. The value of occurrence must be positive. The default values of p_occurrence is 1, meaning the function searches for the first occurrence of substring.

-- --> 6
Select Instrb('Tiếng Việt', 'n') from dual;

-- --> 11
Select Instrb('Tiếng Việt', 'ệ' ,5) from dual;

See also:

• LENGTHB
• INSTR

Syntax:

LENGTH( p_string )

Arguments:

p_string   (CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB)

• Is the string of characters to find the length of. If p_string is NULL or empty, then the LENGTH function returns NULL.

Example:

-- --> 7
Select Length('Tom Cat') As Column1 From Dual;

The LENGTHB function returns the length of the specified string, using bytes instead of characters.

Syntax:

LENGTHB( p_string )

Arguments:

p_string   (CHAR, VARCHAR2, NCHAR, NVARCHAR2)

• The string to return the length (in bytes).

Example:

-- Null - Null
Select Lengthb(Null) Column1
      ,Lengthb('') Column2
From   Dual;

-- 7
Select Lengthb('Tom Cat') Column1 From dual;

-- 2
Select Lengthb('Â') As Column1 From Dual;

-- length of characters
-- --> 10
Select Length('Tiếng Việt') from dual;

-- length of bytes
-- --> 14
Select Lengthb('Tiếng Việt') from dual;

The LPAD function pads (adds characters to) the left-side of a string with a given set of characters.

Syntax:

LPAD( p_string, p_padded_length [, p_pad_string] )

Arguments:

p_string

• The string to pad characters to (the left-hand side).

p_padded_length

• The number of characters to return. If the padded_length is smaller than the original string, the LPAD function will truncate the string to the size of p_padded_length.

p_pad_string

• Optional. This is the string that will be padded to the left-hand side of p_string. If this parameter is omitted, the LPAD function will pad spaces to the left-side of p_string.

Example:

-- 00123
Select Lpad('123', 5, '0')  From   Dual;

-- 12345
Select Lpad('1234567', 5, '0') From Dual;


-- '       Tom'
Select Lpad('Tom', 10) From   Dual;

-- 'To'
Select Lpad('Tom', 2) From   Dual;

The Oracle LTRIM function removes leading spaces from a string or column. Space characters are the default, but you can also specify the character(s) to be removed. Characters can be specified as literals ('A', 'm', etc) or by their CHR equivalents. For example 'CHR(112)', 'CHR(68)'.

The LTRIM function returns a value with datatype VARCHAR2.

Syntax:

LTRIM( p_string [, p_trim_string] )

Arguments:

p_string

•  The string to trim the characters from the left-hand side.

p_trim_string

• Optional. The string that will be removed from the left-hand side of p_string. If this parameter is omitted, the LTRIM function will remove all leading spaces from p_string.

Example:

-- --> TOM
Select LTRIM('   TOM') From dual;

-- --> 789
Select LTRIM('000789', '0') From Dual;


-- --> ABC01
Select LTRIM('0101ABC01', '01') From dual;


-- --> 4210ABC
Select LTRIM('34210ABC', '0123') From dual;

The Oracle RTRIM function removes trailing spaces from a string or column. Space characters are the default, but you can also specify the character(s) to be removed. Characters can be specified as literals ('A', 'm', etc) or by their CHR equivalents: 'CHR(112)', 'CHR(68)'.

The RTRIM function returns a value with datatype VARCHAR2.
 

Syntax:

RTRIM( p_string [, trim_string ] )
 

Arguments:

p_string

• The string to trim the characters from the right-hand side.

p_trim_string

•  Optional. The string that will be removed from the right-hand side of p_string. If this parameter is omitted, the RTRIM function will remove all trailing spaces from p_string.

Example:

-- --> TOM
Select RTRIM('TOM   ') From dual;

-- --> 000789
Select RTRIM('00078900', '0') From Dual;


-- --> 0101ABC
Select RTRIM('0101ABC0101', '01') From dual;


-- --> ABC34
Select RTRIM('ABC34210', '0123') From dual;

The Oracle TRIM function removes leading and trailing spaces from a string or column. Space characters are the default, but you can also specify the character(s) to be removed. Characters can be specified as literals ('A', 'm', etc) or by their CHR equivalents: 'CHR(112)', 'CHR(68)'.

The TRIM function returns a value with datatype VARCHAR2.

Syntax:

TRIM( [ [ LEADING | TRAILING | BOTH ] p_trim_character FROM ] p_string )

Arguments:

LEADING

• The function will remove p_trim_character from the front of p_string.

TRAILING

•  The function will remove p_trim_character from the end of p_string.

BOTH

•  The function will remove p_trim_character from the front and end of p_string.

p_trim_character

•  The character that will be removed from p_string. If this parameter is omitted, the TRIM function will remove space characters from p_string.

p_string

• The string to trim.

Example:

-- --> TOM
Select TRIM('  TOM   ') From dual;


-- --> 78900
Select TRIM(Leading '0' from '00078900') From Dual;


-- --> 001ABC
Select TRIM(Trailing '0' from '001ABC00') From dual;


-- --> ABC21
Select TRIM(Both '0' from '00ABC21000' ) From dual;

The Oracle SUBSTR (Or SUBSTRING) function lets you extract a portion of a string (a 'substring') from a string.

Syntax:

SUBSTR( p_string, p_start_position [, p_length ] )

Arguments:

p_string

• The source string.

p_start_position

• The starting position for extraction. The first position in the string is always 1.

p_length

• Optional. It is the number of characters to extract. If this parameter is omitted, the SUBSTR function will return the substring from p_start_position to end of p_string.

-- 'is is'
Select substr('this is a text message',3,5) Column1 from dual;

-- 'is is a text message'
Select substr('this is a text message',3) Column1 from dual;

-- 'messa'
Select substr('this is a text message',-7,5) Column1 from dual;

-- 'message'
Select substr('this is a text message',-7) Column1 from dual;

-- 'age'
Select substr('this is a text message',-3, 5) Column1 from dual;

-- null
Select substr('this is a text message',-3, -5) Column1 from dual;

Returns a substring counting bytes rather than characters

Syntax:

SUBSTRB( p_string, p_start_position [, p_length ] )

Arguments:

p_string

• The source string.

p_start_position

• The starting position for extraction. The first position in the string is always 1.

p_length

• Optional. It is the number of characters to extract. If this parameter is omitted, the SUBSTRB function will return the substring from p_start_position to end of p_string.

Example:

-- ây là
Select Substrb('Đây là Tiếng Việt', 3, 7) from dual;


-- y là Ti
Select Substr('Đây là Tiếng Việt', 3, 7) from dual;