SQL Server database-basic functions

Question 1:

A company printed a batch of rechargeable cards and the card password was randomly generated. Now this problem occurs:
The "O and 0" (OH and 0) "I and 1" (AH and 1) in the card. The user reports cannot be clearly understood, and the company decides, change all "oh" in the password stored in the database to "zero", and change all "I" to "1 ";

Write SQL statements to meet the preceding requirements;
Database Table Name: card; password field name: password;

 

Analysis:

This is an update statement, which must be an update statement;
Because string replacement is involved, the replace function in SQL Server must be used;

If two rows of SQL statements are used

Update card SET Password = Replace (password, 'O', '0 ')
Update card SET Password = Replace (password, 'I', '1 ')

If a line is used, it can be written as follows:

Update card
Set Password = Replace (replace (password, 'O', '0'), 'I', '1 ')

 

Question 2:

A database table contains the following characters, for example:
13-1, 13-2, 13-3, 13-10, 13-100, 13-108, 13-18, 13-11, 13-15, 14-1, 14-2

Now, you want to sort data by SQL statements. First, you need to sort data by the numbers in the first half, and then sort data by the numbers in the second half. The output is arranged as follows:
13-1, 13-2, 13-3, 13-10, 13-11, 13-15, 13-18, 13-100, 13-108, 14-1, 14-2

Database Table Name: sellrecord; field name: listnumber;

 

Analysis:

This is a query statement. Select statements are required.
You need to use order by for sorting, and in the order by sorting column, you also need to re-calculate the number of sorting
For the number in the first half, you can find the position of the "-" symbol first, take the left half of it, and then use the convert function to convert it to a number:
Convert (INT, left (listnumber, charindex ('-', listnumber)-1 ))
The second half of the number, you can first find the position of the "-" symbol, and then replace all the characters from the first position to this position with spaces, finally, convert it to a number using the convert function:
Convert (INT, stuff (listnumber, 1, charindex ('-', listnumber ),''))

 

The final code is as follows:

Select listnumber
From sellrecord
Order
Convert (INT, left (listnumber, charindex ('-', listnumber)-1 )),
Convert (INT, stuff (listnumber, 1, charindex ('-', listnumber ),''))

 

 

Date functions:

 

Mathematical functions:

 

System functions:

 

String functions:

 

(We can see it on the PPT of Peking University qingniao)