Database SQL Statements Learning notes (6)-Working with data using functions

1.SQL can also use functions to process data, functions are generally performed on the data, for the conversion and processing of data to provide convenience. But each database management system (DBMS) has a specific function, in fact, only a few functions are supported by all DBMS equivalents. For example

MySQL uses the substring () function to extract the components of a string, and Access uses mid ()

MySQL uses the CONVERT () function for data type conversion, DB2 using CAST ()

MySQL uses the curdate () function to get the current date, SQLite uses date ()

Conclusion: SQL statements are not portable

2.SQL using Functions

• Text-handling functions: a RTrim () function was introduced in the previous note to remove the spaces to the right of the column values;
  

  SELECT from Products;

•  select  upper   (prod_name)  from  products; 

  From the above results can be known, function upper () convert text to uppercase
  Other text processing functions example:

  UPPER	Converts a string to uppercase
  left	returns the character to the ieft of the string
  Length	returns the string length
  LOWER	Convert a string to lowercase
  LTRIM	Remove space to the left of the string
  RTRIM	Remove space to the right of the string
  Right	returns the character to the left of the string
  SOUNDEX	Returns the SOUNDEX value of a string

  Note: The Left,right function requires a number of arguments, a few on the left, and several on the right. For example, left (' haha ', 3) indicates that the return string is three characters ' hah '
  Soundex is an algorithm that converts any text to an alphanumeric pattern that describes its speech representation.

• Date and time handlers: (Note your own DBMS, shown here for example MySQL)
  

  SELECT from WHERE year (order_date) =;

• Numeric processing functions:
  

  Function	Description
  ABS ()	Absolute
  COS ()	Cosine
  EXP ()	Index
  PI ()	Pi
  SIN ()	Sinusoidal
  SQRT ()	Square root
  TAN ()	Tangent

Database SQL Statements Learning notes (6)-Working with functions for data