LENGTH
LENGTH function returns the length of the specified string, expressed as the number of characters.
Syntax length( string )
Start-Tech Academy
LENGTH
LENGTH function returns the length of the specified string, expressed as the number of characters.
SELECT Customer_name, Length (Customer_name) as characters
Example FROM customer
WHERE age >30 ;
Start-Tech Academy
UPPER & LOWER
UPPER/ LOWER function converts all characters in the specified string to uppercase/ lowercase.
upper( string )
Syntax
lower( string )
Start-Tech Academy
UPPER & LOWER
UPPER/ LOWER function converts all characters in the specified string to uppercase/ lowercase.
SELECT upper('Start-Tech Academy');
Example
SELECT lower('Start-Tech Academy');
Start-Tech Academy
REPLACE
REPLACE function replaces all occurrences of a specified string
replace( string, from_substring, to_substring )
Syntax Replace function is case sensitive.
Start-Tech Academy
REPLACE
REPLACE function replaces all occurrences of a specified string
SELECT
Customer_name,
Example country,
Replace (country,’United States’,’US’) AS country new
FROM customer;
Start-Tech Academy
TRIM,LTRIM & RTRIM
TRIM function removes all specified characters either from the beginning or the end of a string
RTRIM function removes all specified characters from the right-hand side of a string
LTRIM function removes all specified characters from the left-hand side of a string
trim( [ leading | trailing | both ] [ trim_character ] from string )
Syntax rtrim( string, trim_character )
ltrim( string, trim_character )
Start-Tech Academy
TRIM,LTRIM & RTRIM
TRIM function removes all specified characters either from the beginning or the end of a string
RTRIM function removes all specified characters from the right-hand side of a string
LTRIM function removes all specified characters from the left-hand side of a string
SELECT trim(leading ' ' from ' Start-Tech Academy ');
SELECT trim(trailing ' ' from ' Start-Tech Academy ');
Example SELECT trim(both ' ' from ' Start-Tech Academy ');
SELECT trim(' Start-Tech Academy ');
SELECT rtrim(' Start-Tech Academy ', ' ');
SELECT ltrim(' Start-Tech Academy ', ' ');
Start-Tech Academy
CONCAT
|| operator allows you to concatenate 2 or more strings together
string1 || string2 || string_n
Syntax
Start-Tech Academy
CONCAT
|| operator allows you to concatenate 2 or more strings together
SELECT
Customer_name,
Example city|| ' , '||state|| ' , '||country AS address
FROM customer;
Start-Tech Academy
SUBSTRING
SUBSTRING function allows you to extract a substring from a string
substring( string [from start_position] [for length] )
Syntax
Start-Tech Academy
SUBSTRING
SUBSTRING function allows you to extract a substring from a string
SELECT
Customer_id,
Customer_name,
SUBSTRING (Customer_id FOR 2) AS cust_group
FROM customer
WHERE SUBSTRING(Customer_id FOR 2) = ‘AB’;
Example SELECT
Customer_id,
Customer_name,
SUBSTRING (Customer_id FROM 4 FOR 5) AS cust_number
FROM customer
WHERE SUBSTRING(Customer_id FOR 2) = ‘AB’;
Start-Tech Academy
STRING AGGREGATOR
STRING_AGG concatenates input values into a string, separated by delimiter
string_agg (expression, delimiter)
Syntax
Start-Tech Academy
STRING AGGREGATOR
STRING_AGG concatenates input values into a string, separated by delimiter
SELECT
order_id ,
Example STRING_AGG (product_id,‘, ')
FROM sales
GROUP BY order_id;
Start-Tech Academy
