Tutorial: SQL entry exercise student achievement 6. use SQL Functions

In this lesson, we will learn how to use the built-in SQL System functions to complete the query job. SQL System functions are commonly used, including string functions, date functions, Aggregate functions, and data type conversion, auto-increment functions, and null functions in other function frameworks. The several functions mentioned later have been used in the previous sections. Today we will learn the first two functions.

String functions:

1. charindex ():

Returns the starting position of the specified expression in the string. The formula is charindex ('expression', field name [, start position]). For example, charindex ('shortest ', name, 3) is used) the position found after the third character of the field name. If no small word exists in a record of the Name field, 0 is returned.

The following is a previous question about this function:

The student information of Dalian is located in the region where the student is located:

Select * from student where charindex ('dalian ', diqu)> 0

(Note: Use> 0 to filter out records that do not contain Dalian)

Equivalent to: Select * from student where diqu like '% Dalian %'

2. Left ():

The left formula (field name, truncation length), for example, left (name, 4) intercepts the first 4 characters from left to each record in the Name field.

Query the last names of all student information:

Select left (ltrim (name), 1) from student

(Note: Use the ltrim () function to remove all spaces on the left of the name field)

3. Right ():

The usage is the same as that of left. Right (name, 1) captures 1st characters from the right of each record in the Name field.

4. Len ():

If the formula is Len (field name), such as Len (name), the length of the value of the Name field in each record is obtained, excluding trailing spaces.

Query the names of all students, that is, no last name is displayed.

Select right (rtrim (name), Len (ltrim (name)-1) from student

(Note: Use the rtrim () function to remove all spaces on the right of the name, and then get the character length of the value minus one, that is, the length to be truncated for each record, in this way, the result set with the last name being one word but the number of words in the name being unlimited can be obtained)

5. Lower ():

If the formula is lower (field name), the result set is changed to lowercase for all letters in the field.

Query the names of all students. If the names contain uppercase letters, replace them with lowercase letters.

Lower (name) from student

6. Upper ():

Use the same as lower () to convert all letters in the field into uppercase result sets.

7. Replace ():

Replace the result set of the second expression in the first expression with the third expression, such as Replace (name, '小', 'day '), replace all the 'shares' In the Name field with 'day '.

All student information is displayed. If the gender is male, the information is changed to female.

Select Replace (sex, 'male', 'female ') from student

8. patindex ():

The formula patindex (value, field a) returns the starting position of the first occurrence of the specified value in field.

Query student information with small characters in all student names

Select * from student where patindex ('% small %', name)> 0

Equivalent to: Select * from student where name like '%'

9. Reverse ():

Formula reverse (field name) to flip all values in the field.

10. substring ():

Substring (field A, the starting position of the truncation, and the length of the truncation ).

Query the surnames of all students.

Select substring (ltrim (name), 1, 1) from student

 

Date Functions

Allowed data type: timeinterval indicates the time interval type,CodeIs:

"Y" indicates "year"

"M" indicates "month"

"D" indicates "day"

"H" indicates "hour"

"N" indicates "points"

"S" indicates "seconds ".

1. dateadd ():

The formula dateadd (time type, increment, field name), for example, dateadd (M, 3, admission time) adds 3 to the original month in the admission time field.

Implementation: The admission date of Students in Dalian is pushed to the next five days.

Update student set datetime = dateadd (d, 5, datetime) Where diqu = 'dalian'

2. datediff ():

Formula dateiff (time type, start time, end time), such as select datediff (M, '2017-1-1 ', '2017-1-1 ') the number of months in the end time and start time is 144.

3. datename ():

The formula datename (time type, field name), such as datename (D, admission time) will get the day of all students admission.

Implementation: query the number of months on which all students enter school.

Select datename (M, datetime) from student

The expected month is ,.

4. datepart ():

The usage is the same as that of datename (). Use this function to complete the preceding query.

Select datepart (M, datetime) from student

The expected month is 5, 6, 7 ......, The description returns an integer of the date part.

5. Day (), month (), Year ():

If the formula is day (field name), all days in the field are obtained.

Use the month () method to implement the preceding Query

Select month (datetime) from student

The result is the same as datepart (), which is the number of months in an integer.

6. getdate ():

Obtain the current system time.

7. getutcdate ():

Returns the current UTC time.

Select getdate (), getutcdate ()

16:54:43. 560 08:54:43. 560