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