SQL Server Built-in Functions: String, Math, Date, and Wildcards, Lecture notes of Introduction to Database Management Systems

Download SQL Server Built-in Functions: String, Math, Date, and Wildcards and more Lecture notes Introduction to Database Management Systems in PDF only on Docsity!

Built-in Functions

Functions and Wildcards in SQL Server

1. Function Overview

2. String Functions

3. Math Functions

4. Date Functions

5. Other Useful Functions

6. Wildcards

Table of Contents

• String Functions – for manipulating text
  • Either Table values or user input
  • E.g. concatenate column values
• Math Functions – calculations and working with aggregate

data

• E.g. perform geometry and currency operations
• Date and Time Functions
• E.g. find length of timespan
• Miscellaneous Functions SQL Functions

STRING FUNCTIONS

CONCAT, SUBSTRING, Etc.

• SUBSTRING – extract part of a string
• Example: get short summary of article String Functions (2) SUBSTRING( String , StartIndex , Length ) Index is 1-based! SELECT ArticleId, Author, Content, SUBSTRING(Content, 1, 200) + '...' AS Summary FROM Articles SUBSTRING('SoftSchool', 5, 6) School

• REPLACE – replace specific string with another
• Example: censor the word blood from album names String Functions (3) REPLACE( String , Pattern , Replacement ) SELECT REPLACE(Title, 'blood', '*****') AS Title FROM Album REPLACE('SoftSchool', 'Soft', 'Hard') HardSchool

• LEFT & RIGHT – get characters from beginning or end of

string

• Example: name shorthand (first 3 letters) String Functions (5) LEFT( String , Count ) RIGHT( String , Count ) SELECT Id, Start, LEFT(Name, 3) AS Shorthand FROM Games

• Our database contains credit card details for customers
• Provide a summary without revealing the serial numbers Problem: Obfuscate CC Numbers ID FirstName LastName PaymentNumber 1 Guy Gilbert 5645322227179083 2 Kevin Brown 4417937746396076 … … … ID FirstName LastName PaymentNumber 1 Guy Gilbert 564532 ********** 2 Kevin Brown 441793********** … … …

• LOWER & UPPER – change letter casing
• REVERSE – reverse order of all characters in string
• REPLICATE – repeat string String Functions (6) LOWER( String ) UPPER( String ) REVERSE( String ) REPLICATE( String, Count )

• CHARINDEX – locate specific pattern (substring) in string
• STUFF – insert substring at specific position String Functions (7) CHARINDEX( Pattern, String, [ StartIndex ]) STUFF( String, StartIndex, Length, Substring ) Optional, begins at 1 Number of chars to delete

• SQL Server supports basic arithmetic operations
  • Addition, subtraction, etc.
• Example: find area of triangles by given side and height Math Functions SELECT Id, (AH)/2 AS Area FROM Triangles*

• PI – get the value of Pi as float (15 – digit precision)
• ABS – absolute value
• SQRT – square root (result will be float)
• SQUARE – raise to power of two Math Functions (2) SELECT PI() --3. ABS( Value ) SQRT( Value ) SQUARE( Value )

• POWER – raise value to desired exponent
• ROUND – obtain desired precision
  • Negative precision rounds characters before decimal point
• FLOOR & CEILING – return the nearest integer Math Functions (3) ROUND( Value , Precision ) POWER( Value , Exponent ) FLOOR( Value ) CEILING( Value )

• Calculate the required number of pallets to ship each item
  • BoxCapacity specifies how many items can fit in one box
  • PalletCapacity specifies how many boxes can fit in a pallet Problem: Pallets