SQL Basic Functions Chapter2

LTRIM and RTRIM Functions

Ø  LTRIM Function

Syntax:

SELECT LTRIM (' Amit') AS Names

Result:

Names

Amit

LTRIM in sql server removes blanks from the beginning (left) of a string. For example, if three blank spaces appear to the left of a string such as ' Amit', you can remove the blank spaces with the above query.

It does not matter how many blank spaces precede the non-blank character. All leading blanks will be excised.

Ø  RTRIM Function

Syntax:

SELECT RTRIM ('Amit ') + Bhardwaj AS Names

 Result:

Names

Amit Bhardwaj

Similarly, RTRIM in sql server removes blanks from the end (right) of a string. For example, if blank spaces appear to the right of Amit in the names column, you could remove the blank spaces using the RTRIM, and then concatenate "Bhardwaj" with the + sign, as shown above.

 

Length Function

 Syntax:

Len(string)
OR SELECT LEN(column_name) FROM tbl_name

Output:

SELECT Len ("Learnsqlserver")

would return 14

 

Use of LIKE in SQL Query

 The LIKE in sql server allows you to use wildcards in the where clause of an SQL server statement. This allows you to perform pattern matching.
You can use LIKE condition with in your select, insert, update, or delete sql statements.
The patterns that you can choose from are:

% allows you to match any string of any length.
_ allows you to match on a single character

E.g

SELECT * FROM tbl_Student WHERE sname LIKE '%Amit%'

 

Date Function

Syntax

SELECT GETDATE(), -- Current Date and Time
CURRENT_TIMESTAMP, -- Current Date and Time

Output

SELECT GETDATE()

would return 2011-09-14 20:24:17.060

 

 

REPLICATE Function

The REPLICATE function repeats a given character expression a designated number of times.

Syntax:

REPLICATE ( character_expression ,integer_expression )

Result:

SELECT REPLICATE ('AZ ', 15)

This returns:
AZ AZ AZ AZ AZ AZ AZ AZ AZ AZ AZ AZ AZ AZ AZ

SQL Basic Functions Chapter1

SQL Functions

 Ø  SUM Function: to get sum the values of the column

Syntax:

SELECT SUM (column_name) FROM Table_Name

Example:

SELECT SUM (Hours) FROM tbl_Abc

Ø  AVG Function: to get the avg of the column

SELECT AVG (hours) FROM tbl_Abc

Ø  Min/MAX Function:

Syntax:

SELECT MIN (wage) AS [Minimum Wage],
MAX(wage) AS [Maximum Wage] FROM tbl_Abc

Example:
MIN Function With BETWEEN Operator

SELECT OrderID, MIN(Quantity) as Quantity
FROM [Order Details]
WHERE OrderID BETWEEN 11000 AND 11002
GROUP BY OrderID

OrderID	Quantity
11000	25
11001	6
11002	15

Modify it one more time for the MAX function:

SELECT OrderID, MAX(Quantity) Quantity
FROM [Order Details]
WHERE OrderID BETWEEN 11000 AND 11002
GROUP BY OrderID

OrderID	Quantity
11000	30
11001	60
11002	56

Ø  Round Function

Syntax:

Select Round (number, n)

Example:

SELECT ROUND (1.2536, 3) as Roundof;
SELECT ROUND (1.2536, 2) as roundof;

Result:

Roundof: - 1.2540Roundof: - 1.2500

Ø  Ceiling Function

Syntax:

Ceiling (number value)

 Number value is the value which is used to find the smallest integer value. Example:

Select Ceiling (32.65) as Ceiled                                                   would return, Ceiled: 33
select Ceiling (32) as Ceiled                                                          would return, Ceiled: 32
Select Ceiling (-32.65) as Ceiled                                                  would return, Ceiled: 32
Select Ceiling (-32) as Ceiled                                                        would return, Ceiled: -32

 Ø  Floor Function:

        Syntax:

Floor (Any number)

 Example:

SELECT FLOOR(4.88)

would return 4

 

Ø  Sqrt Function:

Syntax:

sqrt( value)

Example:

SELECT SQRT(9)

would return 3

 

Ø  Top Function:

Syntax:
Select Top (any number) from table name

 Example:

SELECT TOP 5 Name, CreditRating

FROM tbl_Vendor
ORDER BY CreditRating DESC, Name;

Result:

Name	CreditRating
Merit Bikes	5
Victory Bikes	5
Proseware, Inc.	4
Recreation Place	4
Consumer Cycles	3

 Ø  Bottom Function:

Let’s we’ve below table

Name	Wage
Mike	$250
Jack	$328
Gonaa	$450
Darek	$678
jacob	$1,000
Michel	$870 Bottom of Form

 

 

 

Syntax with Example:

SELECT TOP 2 names, wage FROM tbl_Employee
ORDER BY wage DESC

Result:

Name	Wage
Michel	$870
jacob	$1,000 Bottom of Form

Ø  Distinct Function:

Let’s we’ve below table

Category

A

B

C

A

F

None

 

 

 

Syntax:

SELECT DISTINCT columns FROM tables WHERE predicates;

Example:

SELECT DISTINCT Category FROM Tbl_Category;

Result:

Category

Michel

jacob

 Ø  SQL LEFT/RIGHT

Syntax for LEFT:

SELECT names, LEFT (names, 3) AS [left] FROM tbl_Employee

Syntax for RIGHT:

SELECT names, Right (names, 3) AS [Right] FROM tbl_Employee

Names	Left	Right
Sumon Bagui	Sum	gui
Sudip Bagui	Sud	gui
Priyashi Saha	Pri	aha
Ed Evans	Ed	ans