SQL single-row character munipulation functions

Define, syntax and an example with output for

CONCAT(string1, string2)

Combines result from several different fields.

Syntax:
CONCAT(string1, string2)
Example:
SELECT CONCAT(first_name,last_name) FROM employee
WHERE first_name LIKE ‘b%’
Output:
Brad cooper

INITCAP(string)

Returns a string with each word's first character in uppercase and the rest in lowercase.

Syntax:
INITCAP(character-expression)
Example:
select initcap (dept) from employee;
Output:
Analyst

LENGTH(string)

Returns the length of the string. Also called as LEN()in sql server.

Syntax:
LENGTH(string)
Example:
select LENGTH (emp_name) from employee Where emp_id=’1’
Output:
6

LPAD(string, #,padding_char)

Used to pad the string to left to length n characters.string_pad parameter is optional. If not specified, string will be padded spaces to the left-side of string.

Syntax:
lpad ('string', n [, 'string_pad')
Example:
lpad('tech', 7);
Output:
‘ tech’

RPAD(string, #,padding_char)

Used to pad the string to right to length n characters.string_pad parameter is optional. If not specified, string will be padded spaces to the right-side of string.

Syntax:
rpad ('string', n [, 'string_pad')
Example:
rpad('tech', 7);
Output:
‘tech ’

LTRIM(string,searchString)

Used to remove all white spaces from the beginning of the string.

Syntax:
Ltrim(string)
Example:
SELECT LTRIM(' Sample ');
Output:
‘sample ’

RTRIM(string,searchString)

Used to remove all white spaces at the end of the string.

Syntax:
rtrim(string)
Example:
SELECT rTRIM(' Sample ');
Output:
‘ sample’

REPLACE(string,searchString,replacement)

Used to replace the string1, having string2 with string3.

Syntax:
REPLACE(string1, string2, string3)
Example:
SELECT REPLACE(branch, 'st', 'saint') FROM company;

SUBSTR(string,start,length)

Displays the string Starting with the th character in string and select the next characters.

Syntax:
SUBSTR(str,pos,len):
Example:
SELECT SUBSTR(‘San Diego,2,4);
Output:
‘an D’

UPPER(string)

Returns a string in lowercase converted to uppercase

Syntax:
UPPER(string):
Example:
SELECT UPPER(‘sAnd’);
Output:
SAND

LOWER(string)

Returns a string in uppercase converted to lowercase

Syntax:
LOWER(string):
Example:
SELECT LOWER(‘sANd’);
Output:
sand
SQL single-row date functions
SQL single-row date functions - SYSDATE, ADD_MONTHS(Date, months_to_add), LAST_DAY(Date), MONTHS_BETWEEN(Date1, Date2)...
Subqueries
Subqueries - Explain subqueries with an example, A multiple-row subquery with an example, Single-row subqueries with an example...
SQL*Plus
SQL*Plus - What is SQL*Plus? Explain its features, Explain how to use the SQL*Plus utility, What are the basic SQL*Plus commands?, What is AFIEDT.BUF?, Explain how to enable the SQL*Plus HELP facility...
Post your comment

You may like