String functions in SQL

We move on to describe and explain the string functions within sql and divide them into two groups, those that return characters and those that return numbers.

String functions work with char and varchar fields, so the literals we write must be enclosed in single quotes.

These functions that we are going to explain below can manipulate strings of letters or other characters, so we are going to divide them into two groups:

Functions that return characters

These types of functions return a character or several characters.

Function Purpose CHR(n) Returns the character whose binary value is n CONCAT(str1, str2) Returns str1 concatenated with str2 UPPER(str) Converts each to uppercase LOWER(str) Converts each to lowercase LPAD(str1,n) With this function we add characters to str1 from the left up to a maximum length given by n INITCAP(str) Converts the first letter of str to uppercase LTRIM(str ) Removes a set of characters to the left of str, where set is the set of characters to eliminate RPAD(str1, n) With this function we add characters in the same way as with the LPAD function but this time we add them to the right RTRIM(str) Does the same as LTRIM but on the right REPLACE(str,search_string) Substitutes a character set of 0 or more characters, returns str with each occurrence of search_string replaced by replacement_string SUBSTR(str, m) Returns the substring of str that spans from m to the number of characters given by n. TRANSLATE(str1,str2,str3) Converts characters in a string to different characters. Returns str1 with the characters found in str2 and replaced by the characters in str3

Here are some examples of using these functions:

Sql statement that returns the letters whose asccii value is 45 and 23

select CHR(45), CHR(23) FROM TABLE;

Sql statement that obtains the name of the students by displaying the following sentence on the screen: the student’s name is (name that is stored in the table)

select CONCAT (‘the student’s name is’, name) from student;

Sql statement that returns the names of the students in uppercase

select UPPER(name) from student;

Sql statement that obtains from a field name, the first 3 letters

select SUBSTR(name,0,3) from student;

And so with the rest of the functions…

Functions that return numeric values

These functions return numbers to us as information.

Function Purpose ASCII(str) Returns the ASCII value of the first letter of str INSTR(str1, str2]) Function that searches for a set of characters within a string. Returns the position of str2 in str1 starting to search at start LENGTH(str) Returns the number of characters in str

As with the previous functions, we leave some examples so that you can see how it works.

SQL statement that returns the ASCII value of the letter (‘s’)

select ASCII(‘s’) from table;

Statement that returns the position of the occurrence ‘pe’ within the string ‘The dogs are fine’ starting from position 2

select INSTR(‘The dogs are fine’,’pe’,2) from table;

Sql statement that returns the number of characters in the names of the students

select LENGTH(name) from students;