Tag Archives: Statement

SQL ROUND()

SQL ROUND()  is used to round a numeric field to the number of decimals specified.

Syntaxt

SELECT ROUND(column_name,decimals) FROM table_name

Table – Users

+—–+—–+———–+
| oid | uid | price     |
+—–+—–+———–+
|   1 |   1 | 60.345645 |
|   2 |   2 | 30.45564  |
|   3 |   2 | 60.78565  |
|   4 |   1 | 70.96565  |
|   5 |   2 | 80.565645 |
|   6 |   1 | 50.57565  |
+—–+—–+———–+

Example

mysql> SELECT ROUND(price,2) as price  FROM orders;
+——-+
| price |
+——-+
| 60.35 |
| 30.46 |
| 60.79 |
| 70.97 |
| 80.57 |
| 50.58 |
+——-+
6 rows in set (0.00 sec)


SQL MID()

SQL MID()  is used to extract characters from a text field.

Syntaxt

SELECT MID(column_name,start,length) FROM table_name

Table – Users

+—–+——-+
| uid | name  |
+—–+——-+
|   1 | name1 |
|   2 | name2 |
|   3 | name3 |
|   4 | name4 |
+—–+——-+

Example

mysql> SELECT MID(name,1,3) as name  FROM users;
+——+
| name |
+——+
| nam  |
| nam  |
| nam  |
| nam  |
+——+
4 rows in set (0.00 sec)

mysql> SELECT MID(name,2,4) as name  FROM users;
+——+
| name |
+——+
| ame1 |
| ame2 |
| ame3 |
| ame4 |
+——+
4 rows in set (0.00 sec)

SQL LCASE()

SQL LCASE()  converts the value of a field to lowercase.

Syntaxt

SELECT LCASE(column_name) FROM table_name

Table – Users

+——+
| name |
+——+
| AAAA |
| BBBB |
| CCCC |
| DDDD |
| EEEE |
+——+

Example

mysql> SELECT LCASE(name) FROM users;
+————-+
| LCASE(name) |
+————-+
| aaaa        |
| bbbb        |
| cccc        |
| dddd        |
| eeee        |
+————-+
5 rows in set (0.00 sec)