Scribd
Upload
25 views3 pages

L01.2 - Functii Predefinite SQL Server

This document contains examples of SQL functions for performing various types of calculations and string manipulations. It includes math functions like addition, subtraction, multiplication, division and trigonometric functions. It also includes functions for working with dates, strings like concatenation, conversion and manipulation. Sample functions demonstrated include ROUND, DATEPART, REPLACE, LEFT, RIGHT, SUBSTRING and more.

Uploaded by

Husker Dou
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
25 views3 pages

L01.2 - Functii Predefinite SQL Server

This document contains examples of SQL functions for performing various types of calculations and string manipulations. It includes math functions like addition, subtraction, multiplication, division and trigonometric functions. It also includes functions for working with dates, strings like concatenation, conversion and manipulation. Sample functions demonstrated include ROUND, DATEPART, REPLACE, LEFT, RIGHT, SUBSTRING and more.

Uploaded by

Husker Dou
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

select 5+3*7

select 5+3*7 as [Calcul artimetic]


select PI()
select DEGREES(2*PI())
select DEGREES(PI()/6)
select RADIANS(180.0)
select RADIANS(30.0)
select SIN(RADIANS(30.0))
select COS(RADIANS(60.0))
select TAN(RADIANS(45.0))
select COT(RADIANS(45.0))
select TAN(RADIANS(90.0))
select DEGREES(ACOS(0.5))
select ACOS(0.5)
select ACOS(0.5)*3
select ASIN(0.5)
select DEGREES(ASIN(0.5))
select ATAN(1)
select DEGREES(ATAN(1))

--ATN2(y,x) = arctan(y/x)
select DEGREES(ATN2(1,1))
select DEGREES(ATN2(1,-1))
select DEGREES(ATAN(-1))
select DEGREES(ATN2(-1,-1))
select DEGREES(ATN2(-1,1))

--Functia exponentiala
select EXP(1)
select EXP(2)

--Functia logaritmica
select LOG(EXP(2))
select LOG10(1000)
select EXP(LOG(2))

--Functia putere
select POWER(2,4)
select POWER(8.0,1.0/3.0)

--Functia radical
select SQRT(25)
select SQRT(26)

--Functia patrat
select SQUARE(5)

--Functii de rotunjire
--Functia CEILING - cel mai mic nr intreg mai mare decat argumentul
select CEILING(7.53)
select CEILING(7.28)
select CEILING(7.0)

--Functia FLOOR - cel mai mare nr intreg mai mic decat argumentul
select FLOOR(7.53)
select FLOOR(7.28)
select FLOOR(7.0)

--Functia ROUND
--ROUND(expresie,nr zecimale[,mod rotunjire])
--mod rotunjire are valoarea implicita 0 si inseamna rotunjire propriu-zisa
--mod rotunjire !=0 inseamna trunchiere
select ((7+8+8)/3.0)
select ROUND((7.0+8+8)/3,2) -- gresit - 7,67
select ROUND((7.0+8+8)/3,2,1) -- 7,66
select ROUND(7.54325,0) -- 8
select ROUND(7.5000,0) -- 8
select ROUND(7.25732,0) -- 7

--Functia RAND (random) - genereaza numere aleatoare intre 0 si 1


select RAND()
select ROUND(RAND()*100,0) -- genereaza numere aleatoare intre 0 si 100
select ROUND(RAND()*200,0)-100 -- genereaza numere aleatoare intre -100 si 100

--Lucru cu siruri de caractere


select ASCII('a')
select ASCII('A')
select CHAR(97)
select CHAR(172)
select LEN('abcd')
select LOWER('abcdXYZ') -- transforma in litere mici
select UPPER('abcdXYZ') -- transforma in litere mari
select LEFT('abcdefgh',3) -- abc
select RIGHT('abcdefgh',3) -- fgh
select SUBSTRING('abcdefgh',3,2) -- cd
select CHARINDEX('bc','abcabcabc') -- 2
select CHARINDEX('bc','abcabcabc',3) -- 5
select CHARINDEX('bc','abcabcabc',6) -- 8
select CHARINDEX('bc','abcabcabc',9) -- 0
select 'abc'+'xyz' -- abcxyz
select '|' + LTRIM(' Pitesti ') + '|' -- elimina spatiile din stanga
select '|' + RTRIM(' Pitesti ') + '|' -- elimina spatiile din dreapta
select '|' + LTRIM(RTRIM(' Pitesti ')) + '|' -- elimina spatiile din stanga
si dreapta
select REPLACE('abcdabcdabcd','bc','xyz') -- inlocuim 'bc' cu 'xyz' peste tot

select '|' + REPLACE(LTRIM(RTRIM(' Pitesti Mioveni ')),' ',' ') + '|'

--Functia STUFF
select STUFF('mere',1,2,'mu') -- mure
select STUFF('informatica',6,6,'matician') -- informatician

--Functia REPLICATE
select REPLICATE('*',8) -- ********
select REPLICATE('abc',5) -- abcabcabcabcabc
select '|' + SPACE(20) + '|'

--Functia STR - converteste un nr in string


select STR(357.48321,15,2) -- 357.48
select STR(357.48721,15,2) -- 357.49
select REPLACE(STR(357.48721,15,2),' ','*')
select REVERSE('abc') -- cba
select REVERSE(46783) -- 38764

--Functii de lucru cu date calendaristice


select GETDATE() as acum
select GETDATE() + 1 as maine
select GETDATE() - 1 as ieri
select YEAR(GETDATE()) as an
select MONTH(GETDATE()) as luna
select day(getdate()) as zi
select DATEPART(year,getdate())
select datepart(month,getdate())
select datepart(day,getdate())
select datepart(hh,getdate())
select datepart(hour,getdate())
select datepart(minute,getdate())
select datepart(MI,getdate())
select datepart(n,getdate()) -- tot minute
select datepart(ss,getdate())
select datepart(second,getdate())
select datepart(mm,getdate()) -- luna
select datepart(ms,getdate()) -- milisecunde
select datepart(dy,getdate()) -- a cata zi din an
select datepart(dw,getdate()) -- a cata saptamana din luna
select datepart(w,getdate())
select datepart(ww,getdate()) -- a cata saptamana din an
select datepart(Q,getdate()) -- trimestru

--google tsql datepart -> variante de a extrage informatii dintr-o data


calendaristica
[email protected]

You might also like