SQL learning uses commonly used functions to process data

First, before the introduction of the use of functions to process data, the use of DBMS (database management system) to deal with the data caused by the problem!

1. Unlike almost all DBMS support for SQL statements (such as Select), each DBMS has a specific function, in fact, only a few functions are equivalent to all major DBMS support.

For example, as part of extracting strings , Acess uses mid ();D B2, Oracle, Postgre, and SQLite to use substr (), while MySQL and SQL Server use substring ();

For example, the conversion of data types,access and Oracle use multiple functions, each type of conversion has a function; DB2 and PostgreSQL use CAST (); MariaDB, MySQL, and SQL Server use CONVERT ();

For example, to get the current date , Access uses now ();D B2 and PostgreSQL to use current_date; MARIADB and MySQL use Curdate (); Oracle uses sysdate,SQL Server uses getdate (), and SQLite uses date ();

As we can see from the above example, unlike SQL statements, SQL functions cannot be ported. This means that the code written by this particular SQL implementation may not be normal in other implementations.

So if you are writing a SQL program, you need to consider the portability of the program, then you must be careful when using the function, because these functions, in some circumstances may not be implemented, so you write the SQL code must write good comments, to say that the name of this code is what to use! To facilitate the subsequent development or migration between libraries!

2. The following functions are supported by most SQL implementations

(1) A text function for handling text strings (such as deletions or fills, converting values to uppercase or lowercase).

(2) A numeric function used to perform arithmetic operations on numeric data, such as returning absolute values and doing algebraic operations.

(3) Date and time functions for processing date and time values and extracting specific components from these values, such as the difference between two dates and the validity of the check date.

(4) System functions that return special information that the DBMS is using, such as returning user login information

The following are common text-processing functions

1. Left ()---returns the character

Using left (calculated field, index), index represents the following code for indexing the start of the look- down:

Select   from where cno='c001'

Now I need the string to the left of the CNAME column value ' S ', and here's the workaround code:

Select  Left (CNAME,2fromwhere cno='c001'

OK, complete the demand!

2. Right ()---returns the character to the left of the string

Use the opposite of the Left () method

3, Length () (can also use datalength () or Len ())---Returns the length of a string you can use Datalength and Len () in SQL Server

Usage: The following code:

Select datalength  from where cno='c001'

4. The upper () function of the text processing function---to capitalize the string

Usage: UPPER (calculated field), convert all the column values in the calculated field to uppercase letters, see the following code:

Select Top 5  from Dbo.course

Now there is a need to convert the CNAME column values all to uppercase, the following is the workaround code:

Select Top 5 UPPER  from Dbo.course

OK, demand is complete!

6. lower () function of text processing function---Convert a string to lowercase

Use the opposite of the upper () function!

7, SOUNDEX ()---Returns the SOUNDEX value of the string

Soundex is an algorithm that converts any text string into an alphanumeric pattern that describes its voice representation. Soundex considers similar pronounced characters and syllables, making it possible to pronounce a string rather than a letter comparison! Although SOUNDEX is not a SQL concept, most DBMS provide support for SOUNDEX! The code is as follows:

Select *  from where sname='Michael Green'

This time we forget how to spell the column value of Sname, only remember his pronunciation, this time using the SOUNDEX() function can also retrieve this record, the code is as follows:

Select *  from where SOUNDEX (sname) = SOUNDEX ('Michelle Green')

OK, complete the search!

8. Date and time processing function DATEPART() function

Before using date and time handlers, it is important to note that different DBMS have their own unique time and date formats, so they are very inconsistent and portable and very poor!

The following code is available:

Select *  from Tb_order

There is now a requirement to retrieve all orders for 2016 years, and here is the workaround code:

Select *  from where DATEPART (yy,orderdate) =  .

OK, demand is complete!

9, the following is a commonly used numerical processing function

ABS ()---Returns the absolute value of a number

COS ()---Returns the cosine of an angle

EXP ()---Returns the exponential value of a number

PI ()---return pi

Sin ()---Returns the sine of an angle

SQRT ()---Returns the square root of a number

TAN ()---Returns the tangent of an angle

SQL learning uses commonly used functions to process data