SQL REPLACE() – ROUND() – SPACE() – SUBSTRING() FUNCTIONS

SQL REPLACE() FUNCTION

RESULT

SmallestOrderPrice 300

SQL REPLACE() FUNCTION

The SQL REPLACE() function is used to replace every Occurrence of a specified 

String with a replacement string.

SYNTAX

SELECT REPLACE (column_ name, string_ to_ replace, String_ replacement) 

FROM table_ name

EXAMPLE

O-Id          OrderDate          OrderPrice         Customer

1       2011/11/07              1200                Johnson

2               2011/08/15              1500                Brown

3               2011/05/04                600                Johnson

4               2011/07/30 2500               Taylor

5       2011/10/08               300                 Brown

Replace all records which matches “Brown” in “Customer” column with 

“unknown”.

EXAMPLE

SELECT O_ Id,OrderDate,OrderPrice,REPLACE(Customer, ‘Brown’, ‘unknown’)

FROM Orders

RESULT

O-ld   OrderDate      OrderPrice            Customer

  1     2011/11/07         1200                     Johnson

  2     2011/08/15         1500                    unknown

  3     2011/05/04          600               Johnson

  4     2011/05/21          500                      Johnson

  5     2011/07/30         2500                       Taylor

  6     2011/10/08          300                      unknown

SQL ROUND() FUNCTION

SQL ROUND() FUNCTION

The ROUND() function is used to round a numeric field to the number of 

decimals specified.

SYNTAX

SELECT ROUND(column_name,decimais) FROM table _name

EXAMPLE

Pr_id           ProductName       Unit          UnitPrice

1                   Potatoes             1 kg            2.38

2                    PearS                1 kg            4.51

3                   Cherries              1 kg           5.98

Now we want to display the product name and the price rounded to the nearest 

integer.

EXAMPLE:

SELECT ProductName,ROUND(UnitPrice,0) as UnitPrice FROM Products.

RESULT

ProductName        UnitPrice

Potatoes                     2

Pears                          5

Cherries                      6

SQL SPACE() FUNCTION

SQL SPACE() FUNCTION

SQL SPACE() function is used to return space characters, the number of the 

characters is indicated by the integer parameter.

SYNTAX

SELECT column _name SPACE(number_ of_spaces) FROM table_name

EXAMPLE

SELECT ‘ Hello’ +SPACE(5)+’World!’

RESULT

Hello     World!

SQL SUBSTRING() FUNCTION

SQL SUBSTRING() FUNCTION

The SUBSTRING() function is used to get part of a string.

SYNTAX

This function is called differently for the different databases.

MySQL Syntax

MySQL supports two ways of using this function:

SYNTAX

1.SUBSTR(column_ name, start_ position[, length]) FROM table_ name

2.SUBSTRING(column_ name, start_ positionl[, length]) FROM table_ name:

EXAMPLE

The “Persons table:

P_Id          LastName         FirstName        Address           City

1         Jameson              John              Streets           Sander

                                                                     15

2                 Smith                   Kate               Green            Sander

                                                                        68

3                Kristensen            Olya                Ski 2              Stavn

Now we want to get the first characters from “FirstName” Column.

EXAMPLE

SELECT LastName, SUBSTR(FirstName, 1, 1) AS Initial FROM PersonS

RESULT

LastNam.     Initial

JameSon        J

Smith.             K

Kristensen      O