String functions provided by SQL server.

FUNCTION	DESCRIPTION
Expression+expression[+expression]	Concatenates two or more character or binary strings.
ASCII (character_expression)	Returns the ASCII value of the characater expression
CHAR (integer_expression)	Returns the character equivalent of the ASCII code value.

CHARINDEX(‘pattern’, expression)	Returns the starting position of the specified pattern.
DIFFERENCE (character_expression1, character_expression2)	Compares two straing and evaluates the similarity between them on a scale of 1 to 4.
LOWER (character_expression)	Converts two string and evaluates the similarity between them on a scale of 1 to 4.
LTRIM(character_expression)	Returns the data with out leading brackets
PATINDEX(‘%pattern’, expression)	Returns the starting position of the first occurrence of the pattern in the specified expression, zero if the pattern is not found.
REPLICATE(char_expression, integer_expression	Repeats a character expression a specified number of times.
REVERSE(character_expression)	Returns the reverse of character expression.
RIGHT(character_expression, integer_expression)	Returns the part of the character string from the right.
RTRIM(character_expression)	Returns the data without tailing blanks.
SOUNDEX(character_expression)	Returns the four-digit code to evaluate the similarity of two character strings.
SPACE(numeric_expression)	Returns a string of repeated spaces. The number of spaces is equal to the integer expression.
STR(float expression[,length[,decimal]])	Returns character data converted from numeric data.
STUFF(charactaer_expression1,start,length, character_expression)	Deletes length characters from first character exspression at start and then inserts character expression2 into character expression.
UPPER(character_expression)	Converts the character expression into upper case.

Date Functions:

The syntax is:

SELECT date_function(parameters)

FUNCTION	DESCRIPTION
DATEADD(datepart, number, date)	Adds the no.of dateparts to date.
DATEDIFF(datepart, date1, date2)	Returns the number of dateparts between two dates.
DATENAME(datepart, date)	Returns the integer value of the date part.
DATEPART(datepart,date)	Returns the integer value of the date part.
GETDATE()	Returns the current date and time.

Examples:

SELECT GETDATE()

The above statement displays the current system date and time with the help of the GETDATE function.

SELECT DATEDIFF(yy,ord_date,getdate())

This statement uses the DATEDIFF function to find the difference between the current date and the order_date, from sales tables. The difference should be expressed in terms of number of years.

SELECT title, DATEPART(yy,pubdate) FROM  titles.

The above statement uses the DATEPART function to return the year when the book was published, along with the title name.

SELECT Title=title_id, Month=dATENAME(mm,pubdate), year=DATENAME(yy, pubdatae) FROM titles.

Data Conversion:

SQL server handles certain datatype conversion automatically. If a character expression is compared with an int expression, SQL server makes the conversion automatically for the comparison(implicit conversion).

The CONVERT  function is used to change data from one type to another when SQL server cannot implicitly perform a conversion. Using the CONVERT function, data can be modified in variety of styles.

The syntax is:

CONVERT(datatype[(length), expression[,style])

	SELECT Ytd_Sales=CONVERT(char(10),ytd_sales) FROM titles. SELECT CONVERT(int, zip) FROM authors

Aggregate Functions

Aggregate functions are used to produce summary data using tables.

Function	Parameters	Description
AVG	(ALL/DISTINCT] expression	Returns the average of values specified in the expression, either all records or distinct records
SUM	(ALL/DISTINCT] expression)	Returns the sum of values specified in the expression, either all records or distinct records.
MIN	(expression)	Returns the minimum of a value specified in the expression.
MAX	(expression)	Returns the maximum of a value specified in the expression.
COUNT	(ALL| DISTINCT expression)	Returns the number of unique or all records specified in an expression.
COUNT	(*)	Returns the total number of records specified in an expression.

Examples of Aggregate functions

SELECT ‘Average Price”=AVG(price) FROM titles	Returns the average value of all the price values in the titles table with user-defined heading.
SELECT ‘Sum’=SUM(DISTINCT advance) FROM titles	Returns the sum value of all-the unique advance values in the titles table with user-defined heading.
SELECT ‘Minimum Ytd Sales’=MIN(ytd_sales) FROM titles	Returns the minimum value of ytd_sales value in the titles table with user-defined heading.
SELECT ‘Maximum Ytd Sales’=MAX(ytd_sales) FROM titles	Returns the maximum value of ytd_sales in the titles table with user-defined heading.
SELECT ‘Unique Price’=COUNT(DISTINCT price) FROM titles	Returns the number of unique price values in the titles table with user-defined heading.
SELECT ‘Price=COUNT(price)  FROM titles	Returns the number of total number of price values in the titles with user-defined heading.

Selecting Rows

There are situations in which only a few rows need to be retrieved from the table based on a condition. The WHERE clause, is provided by SQL server, to specify a condition.

The syntax for using the WHERE clause is:

SELECT column_list  FROM table_name WHERE search_condition

Example:

SELECT * FROM publishers WHERE  state = ‘MA’

Search Based On Conditions

SQL Server provides few methods of searching rows in a table. These methods can be broadly categorized into the following categories.

Ø       Comparison operators like =, >, <, >=, <=, !=, !< and !>

Ø       Range operators like BETWEEN and NOT BETWEEN.

Ø       List operators line IN and NOT IN.

Ø       String operators like LIKE and NOT LIKE.

Ø       Unknown values like IS NULL and NOT NULL.

Ø       Logical operators like AND , OR and NOT.

Comparison Operator

The command syntax is:

SELECT column_list FROM table_name WHERE expression1 comparison_operator expression2

Valid Comparison operators

Operator	Description
=	Equal to
>	Greater than
<	Less than
>=	Greater than or Equal to
<=	Less than or Equal to
<>, !=	Not Equal to
!>	Not Greater than
!<	Not Less than
()	Controls Precedence

Examples:

	SELECT title FROM titles WHERE type=’business’ SELECT stor_id, ord_num, title_id FROM sales WHERE qty>20 SELECT type, title_id, price FROM titles WHERE price * ytd_sales<advance.

Range Operator

The range operator is used to retrieve data that can be extracted in ranges. The range operations are:

Ø       BETWEEN

Ø       NOT BETWEEN

The syntax is:

	SELECT column_list FROM table_name WHERE expression1 range_operator expression2 AND expression2 Examples: 1. SELECT title from titles WHERE advance BETWEEN 2000 AND 5000 2. SELECT title FROM titles WHERE  advance NOT BETWEEN 4000 AND 5000

List Operator

The syntax is:

	SELECT column_list  FROM table_name WHERE expression list_operator(‘value_list’) Examples: 1. SELECT pub_name, city, state FROM publishers WHERE state IN (‘MA’, ‘DC’) 2. SELECT pub_name, city, state FROM publishers WHERE state NOT IN (‘MA’, ‘DC’)

String Operator

SQL Server provides a pattern-matching method for string expressions using the LIKE keyword with the wildcard characters. The LIKE keyword is used to select those rows that match the specified portion of character string. The LIKE keyword allows wildcard characters that can be used as a part of an expression.

Wild card	Description
%	Represents any string of zero or more characters(s)
-	Represents a single character
[]	Represents any single character within the specified range.
[^]	Represents any single character not within the specified range.

Examples of the LIKE operator with wildcards.

Example	Description
SELECT title FROM titles WHERE type LIKE ‘bus%’	Returns all titles from titles table where first three characters of  the column type are ‘bus’
SELECT *  FROM publishers WHERE country LIKE ‘US_’	Returns all rows from publishers table where country name is three characters long and starts with  US where the third character can be anything.
SELECT title_id, price FROM titles WHERE title_id LIKE ‘P[SC]]%’	Returns all columns from the titles table where title_id starts with the character P and contains S or C in the second position followed by any number of characters.
SELECT title_id, price FROM titles WHERE title_id, LIKE ‘P[^C]%’	Returns all title_id and price from the titles table where title_id starts with P and does not contain and S as the second character and the third position onwards can contain any characters.

Unknown Values

Unknown values refer to the data entered in the form of the  NULL keyword. In SQL serve terms, NULL is an unknown value or the value for which data is not available. The rows containing the NULL values can be retrieved by using the IS NULL keyword in the WHERE clause.

The Syntax is:

SELECT column_list FROM table_name

WHERE column_name unknown_value_operator.

SELECT title, ytd_sales FROM titles WHERE ytd_sales IS NULL

Logical Operator

Ø       OR – Returns the result when any of the specified search conditions is true.

Ø       AND – returns the result when all of the specified search conditions are true.

Ø       NOT – Bnegates the expression that follows it.

SELECT column_list FROM  table_name

WHERE conditional_expression{ANDOR}[NOT] conditional_expression.

Examples of Logical operators

Example	Description
SELECT * FROM publishers WHERE city=’Boston’ OR city=’Paris’	Returns all the rows specific to the conditions, even if any one of the conditions is true.
SELECT publishers WHERE city=’Boston’ AND city=’MA’	Returns all the rows specific to the conditions, when both the conditions are true.
SELECT * FROM  publishers WHERE city=’Boston’ OR NOT  city=’Paris’	Returns all the rows specific to the conditions, except the rows specified with the condition after NOT operator.

Distinct

The distinct clause removes duplicate rows from the result set. Duplicate rows can be eliminated by using the DISTINCT keyword in the SELECT statement.

Syntax:

SELECT [ALL|DISTINCT] column_name FROM table_name WHERE search_condition Example: SELECT DISTINCT title_id FROM titleauthor WHERE title_id LIKE’PS%’

TOP and PERCENT

The TOP clause limits the number of rows returned in the result set.

The syntax is:

TOP n [PERCENT]

Where n specifies the number of rows are to be returned, if PERCENT is not specified. If PERCENT is specified, n specifies the percentage of the rows to be returned.

The TOP clause is used with the SELECT statement.

The following example returns the top 20 rows of the result set.

SELECT TOP 20

The next example returns the 10% of rows from top of the result set.

SELECT TOP 10 PERCENT

It the SELECT statement, including TOP, has  and ORDER BY  clause, then the rows to be returned are selected after the ORDER BY statement has been applied.

Tags: SQL Functions, sql operators