In SQL, there are various data types for string like VARCHAR, CHAR, RAW, VARCHAR2 etc. But, when the SQL character functions are used, the output data type is VARCHAR2.
SQL Character functions
It can be broadly classified into two categories:
- Case Manipulation functions
- Character Manipulation functions
Case Manipulation Functions in SQL :
- LOWER():
LOWER() returns the string with all alpha characters of lower case where as the non alpha characters are remain unchanged
Example:
Query: Select LOWER (‘Sql Tutorial’);
Result: sql tutorial
Query: Select LOWER (‘Sql Tutorial@Padhle’);
Result: sql tutorial @padhle
- UPPER():
UPPER() returns the string with all alpha characters of upper case where as the non alpha characters are remain unchanged
Example:
Query: Select UPPER (‘Sql Tutorial’);
Result: SQL TUTORIAL
Query: Select UPPER (‘Sql Tutorial@Padhle’);
Result: SQL TUTORIAL@PADHLE
Character Manipulation Functions in SQL :
1) Concat(): Contact() function is used to concatenate the two given strings.
Syntax: Contact(String1, String2);
Example:
Query: Select Contact (‘Sql’, ‘Tutorial’);
Result: Sql Tutorial
2) Length():
Length() function takes string expression or column and returns the length of given string expression or column. If column is empty or string has zero characters, it returns NULL.
Syntax: Length(Column or string)
Example:
Query: Select Length (‘Padhle’);
Result: 5
3) Substr():
Substr() functions returns the portion of string with in given specified points
Syntax: Substr(String, start_point, Length_of_string_to_be_extracted)
Example:
Query: Select Substr (‘HelloWorld’,1,5);
Result: Hello
4) Instr():
Instr() functions finds the occurence of substring in a given string
Syntax: Instr(String, Substring)
Example:
Query: Select Instr (‘Hello World , Hello is common world’, ‘common’);
Result: 1
5) Lpad():
Lpad() function pads the given string to a specified length in left side with given string.
Syntax: Lpad(string, length, padded_string)
Example:
Query: Select Lpad (‘world ’10,’ hello’);
Result: helloworld
Rpad():
Rpad() function pads the given string to a specified length in right side with given string.
Syntax: Rpad(string, length, padded_string)
Example:
Query: Select Rpad (‘world ’10,’ hello’);
Result: worldhello
7) Trim():
Trim() trims from start or end or both from the given string. If the character of string to be trimmed is specified, it trims the whitespace from both ends
Syntax: Trim(Leading or Trailing or Both , trim_string FROM string)
Example:
Query: Select Trim (‘H from Hello’);
Result: ello
Query: Select Trim (‘ Hello World ‘);
Result: Hello World
8) Replace():
Replace() string is used to replace the given string with another string in the given text.If replacement string is not given, then the string to be replaced is deleted from text
Syntax: Replace(text,Find_string, replace_string)
Example:
Query: Select replace (‘Hello World’,’Hello’,’Hola’);
Result: Hola World
This is all about the SQL character functions and their description.