Intro To Tsql - Unit 10

Introduction To TSQL Introduction To SQL Unit 10 Unit 10 Modern Business Technology Developed by Michael Hotek

Functions • In addition to the standard SQL statements, SQL Server and most other DBMSs include a set of functions to perform additional processing • The functions fit into 6 categories – – – – – –

string conversion date mathematical aggregate general

Functions • To determine which functions are available for your particular DBMS, you should consult the online help for your DBMS • Overview of Books Online

String Functions • The string functions we will discuss are: – – – – – – – – – – – – – –

substring right upper lower charindex patindex ascii char ltrim rtrim space str soundex replicate

String Functions • Substring gives you the ability to extract a portion of a string from a another string or piece of data substring(expression,start,length) substring('abcde',2,3) Results bcd

• Right returns the right portion of a string right(expression,length) right('abcde',3) Results 'cde

String Functions • Upper converts a string to upper case upper(expression) upper('abcde') Results ABCDE • Lower converts a string to lower case lower(expression) lower('aBCDE') Results abcde

String Functions • charindex returns the starting position of a string the specify charindex(pattern,expression) charindex('bc','abcde' Results 2 • patindex performs the same operation as charindex, but can also include wildcard characters and can be used on text columns patindex(pattern,expression) patindex('%bc%') Results 2

String Functions • ascii converts a character to it's equivalent ASCII value ascii(expression) ascii('T') Results 84 • char takes an ascii value and returns the corresponding character char(expression) char(84) Results T

String Functions • ltrim and rtrim remove leading or trailing spaces. ltrim from the left and rtrim from the right ltrim(expression) ltrim(' abcde') Results abcde rtrim(expression) rtrim('abcde ') Results abcde ltrim(rtrim(' abcde ')) Results abcde

String Functions • Space constructs a string of the number of spaces specifiied space(expression) space(2) Results 2 spaces • Str will convert a number to a string and round it off to the specified number of decimal places str(numeric_value,length,decimals) str(456.56,5,1) Results 456.6

String Functions • soundex converts a character expression to a four character code. This is used to find similar words. It ignores any vowels soundex(expression) soundex('smith'), soundex('smythe') Results 'S530' • replicate creates a string composed of the number of copies of a string that you specify replicate(expression,number) replicate('abc',3) Results abcabcabc

Converting Data • Some data within SQL Server is implicitly converted • A money datatype will convert to a real or a float without any intervention • Sometimes though, we want a number to be treated as a character or vice versa • To do this we need to use the convert function

Convert • The string function str gave us a quick way to convert a number to a string • The convert function gives us much more control over the exact datatype the data is converted to. • convert(datatype,expression [,style]) select title_id, 'The number of books sold: ' + convert(varchar(10),ytd_sales) from titles where title_id like 'PS20%' title_id -------- -----------------------------------PS2091 The number of books sold: 2045 (1 row(s) affected)

Convert • By using the third optional argument of the convert function, you can utilize some formatting while converting data. • This argument is used when converting a date to a string select convert(varchar(8),pubdate,2) from titles where title_id = 'MC3026' Results 91.06.24

Date Conversion

• Some of the most commonly used values are as follows: – – – – – – – –

2 3 4 5 102 103 104 105

yy.mm.dd dd/mm/yy dd.mm.yy dd-mm-yy yyyy.mm.dd dd/mm/yyyy dd.mm.yyyy dd-mm-yyyy

• Consult your online manual for other values

Date Conversion

• A datetime value can be directly compared to a string datatype that has a datetime value, because the string can be implicitly converted select title_id from titles where pubdate = '6/30/86' Results PC1035 • The built in function getdate() will return the current date and time from OS where the DBMS resides

Date Functions • Sometimes we only want a portion of a date or we want to do date arithmetic • To do this we will use the following functions: – – – –

datename datepart dateadd datediff

Date Functions • datename will return the name of a specified part of a date datename(param,date) datename(mm,'2/12/96') Results February • The parameter values are as follows: – – – – – – –

yy qq mm dw dy dd wk

year quarter month day of week day of year day week

Date Functions • datepart will return the numeric part of a date datepart(param,date) datepart(mm,'2/12/96') Results 6 • dateadd adds or subtracts a specified amount of time to a date dateadd(param,number,date) dateadd(dd,5,'6/5/85') Results 6/10/85 • To subtract from a date, specify a negative value: dateadd(dd,5,'6/10/85')

Date Functions • datediff is used to calculate the difference between two dates of times datediff(param,start_date,end_date) datediff(dd,'6/10/85','6/20/85') Results 10 • Available paramters for datediff are: – – – – – – –

yy qq mm dw dd wk hh

year quarted month day of week day week hour

Year 2000 • SQL Server is year 2000 compliant • It accomplishes this by utilizing a comparison technique • If you enter a two digit year less than 50, SQL Server assume 20xx. • If the two digit year is greater than or equal to 50, SQL Server will assume 19xx • If you send it a four digit year, it stores the entire year in the database thus avoiding any confusion.

Mathematical Functions • Mathematical functions allow to you manipulate and do calculations on numeric data. The functions we will examine are: – – – – – – – – – – – – –

abs ceiling floor round exp rand log pi power sqrt sin cos tan

Mathematical Functions • abs returns the absolute value of a number – – – –

abs(number) abs(-59) Result 59

• round returns a value rounded off to the number of digits specified – – – –

round(number,digits) round(123.4567,2) Result 123.4600

• You can round down by specifying a negative value • The result is padded with zeros to keep the total number of digits the same

Mathematical Functions • ceiling returns the next highest integer value – – – –

ceiling(number) ceiling(123.4) Results 124

• floor returns the next lowest integer value – – – –

floor(number) floor(123.4) Results 123

Mathematical Functions • exp raises the constant e to the specified power exp(number) exp(0) Results 1 • log returns the natural logarithm of the specified number log(number) log(1) Results 0

Mathematical Functions • rand returns will generate a set of pseudo-random numbers rand([seed_value]) rand(39) Result

• The rand function is seeded at server startup by the DBMS and uses the system clock as part of the seed value • You can ensure a repeatable sequence by using the same seed value and making sure that only one person accesses the rand function while the repeatable sequence is needed

Mathematical Functions • pi returns the mathematical constant pi – pi() – Results – 3.1415926…

• power raises a number to a specified power – – – –

power(number,power) power(10,3) Results 1000

• sqrt returns the square root of a number – – – –

sqrt(number) sqrt(100) Results 10

Mathematical Functions • sin, cos, and tan return the sine, cosine, and tangent of an angle • The angle is measured in radians and not degrees sin(radians) sin( ) Results

• You can also use the degrees and radians functions to convert values from one to another

Aggregate Functions • We've already touched on most of the aggregate functions previously, but a brief overview follows count(*) count(column) sum(column) avg(column) max(column) min(column)

number of selected rows number of non null rows total of all values in a column average of aq column maximum value in a column minimum value in a column

Isnull • The isnull function can be used to avoid some of the side effects of null values • This function substitute a specified value for a null isnull(expression,value) select title_id, isnull(notes, 'No notes for title') from titles title_id

-------- ----------------------------------------------------------------BU1032 An overview of available database systems with ... BU1111

Helpful hints on how to use your electronic resources to ...

BU2075 BU7832

The latest medical and psychological techniques for living... Annotated analysis of what computers can do for you: ...

MC2222

Favorite recipes for quick, easy, and elegant meals.

MC3021

Traditional French gourmet recipes adapted for modern...

MC3026

No notes for title

... (18 row(s) affected)

Unit 10 Review •

The functions fit into 6 categories – – – – – –

•

string conversion date mathematical aggregate general

You can convert data from one data type to another • Most of the functions you will use are the standard aggregates of count(*), sum, min, and max • Isnull allows a graceful way of handling nulls

Unit 10 Exercises • There are no exercises for this unit