SQL Server

Using Literals

One can use Literals to make output more readable. A literal is a string surrounded by single quotation marks included in the column_list and displayed as another column in the query result

Examples:

SELECT 'Employee Name:',fname +','+lname
FROM employee

OR

SELECT fname, lname, 'Employee ID is' , emp_id
FROM employee

Important Mathematical Functions

Mathematical functions enable one to return mathematical data using the syntax:

• Arithmetic operators can be used with data types: int, smallint, tinyint, numeric, decimal, float, real, money and smallmoney
• With arithmetic operators two types of precedence exists:
  • Datatype Precedence- Arithmetic precedence exists when arithmetic operations are performed on different data types. In case of usage of different data types, the smaller data type is converted to higher data type. Like if we multiply smallint by an int, the result will be an int, but in case of money result will always be of data type money
  • Operator precedence is used when multiple operators are used. It uses same math concept of BODMAS

Select function_name(parameters)

1. Abs (numeric_expr): Provides absolute value

Example. Select Abs (-123)-[123]

2. Ceiling (numeric_expr) : Provides smallest integer greater than or equal to specified value

Example. Select ceiling(123.3), ceiling(-123.3)-[124,-123]

3. Floor (numeric_expr) : Provides largest integer less than or equal to specified value

Example. Select floor (123.3), floor(-123.3.)-[123, -124]

4. Round (numeric_expr, length) : Provides numeric expression rounded to the specified length in an integer value

Example. Select Round (1234.56,1), Round ($123.67,1)-[1234.6,123.7]

5. Square (float_expr) : Provides square value

Example. Select square (9)-(81)

6. SQRT (float_expr) :

Example. Select sqrt (9)-[3]

7. POWER (numeric_expression, y) : Provides value of numeric expression to the power of y

Example. Select Power(2,3)-[8]

Remember when using mathematical functions with monetary data type always use it with a dollar sign otherwise the value will be treated as numeric value

Important String Functions

String functions are used for manipulation of character data type

· + (expression, expression) : Concatenates two or more strings

Example

Select lname +','+ fname +'.' As Name from employee

OR

SELECT ('abc' + 'def') -[abcdef]

· ASCII (char_expr) :Provides ASCII code for left-most character

Example

Select ASCII ('A')-[65]

· Char (integer_expr) : Provides character equivalent of ASCII code value

Example: Select Char(97)-[a]

Control Character	Value
Tab	Char (9)
Linefeed	Char (10)
Carriage return	Char(13)

Example

Use Northwind
GO
SELECT FirstName + ' ' + LastName, + CHAR(13) + Address, + CHAR(13)+ City, Region +char(10)
FROM Employees

· PATINDEX('% pattern%', expression): Returns starting position of first occurance in expression

Example

select patindex('%bv%','ronvcbvvbc')

· Lower (char_expr): Converts to lower case

Example

Select Lower ('ABCD')-[abcd]

· Upper (char_expr): Converts to upper case

Example

Select Upper ('abcd')-[ABCD]

· Left (char_expr, integer_expr) : Provides character string starting from the left and preceeding integer_expr character

Example

Select left('Seattle' ,2) - [se]

OR

USE pubs
GO
SELECT LEFT (title, 5) as Titles
FROM titles

· Right (char_expr, integer_expr) : Provides character string starting from the integer_expr character from Right

Example

Select Right ('Seattle' ,2) --[le]

· LTRIM (char_expr) : Returns data without leading blanks Example

Select Ltrim (' Computers')-[Computers]

· RTRIM (char_expr) : Returns data without trailing blanks

Example

Select Ltrim ('Computers ')-[Computers]

· REPLACE ('string_expression1', 'string_expression2', 'string_expression3') :Replaces all occurrences of srting2 in string1 with string3

Example. Select Replace ('abcde', 'bc','oo') -[aoode]

· Reverse (char_expr) : Returns reverse of character expression

Example

Select Reverse('ABCD')-[DCBA]

· SUBSTRING(expression, start, length): Returns part of character string

Example

SELECT SUBSTRING('abcdef', 2, 3) as X

X
----
bcd