توابع SQL Server

فصل بیست و دوم :

توابع SQL Server

SQL FUNCTIONS

Function ها دستورات تک کلمه ایی هستند که یک مقدار را بعنوان خروجی باز می گردانند . فانکشن می تواند دارای پارامتر های ورودی باشد که مقدار بازگشتی از آن تابع مقدار پارامتر ورودیست و یا بدون پارامتر ورودی باشد
توابع اس کیوال بر دو نوع هستند :

1. توابع داخلی built-in Functions

• Aggregate functions

بروی یکسری مقادیر کار میکنند ولی فقط یک مقدار باز میگردانند .
این توابع اصولا null را نادیده میگیرند . بجز تابع count .
این توابع در فصل ذیل بطور مفصل بحث شده اند :
Aggregate Functions

• Ranking function

یک مقدار رنکینگ برای هر رکورد بر می گرداندند:
این توابع در فصل ذیل بطور مفصل بحث شده اند :
Ranking Functions

Scalar function

بروی یک مقدار کارمیکنند وفقط یک مقدار بازمیگردانند. (توابع تاریخ و زمان ، توابع کاربروی رشته ها ، توابع ریاضی ، توابع سستمی و ...)
تعداد آنها بسیار زیاد است و تنها تعدادی از آنها بشرح ذیل می باشند : به فیلم مراجعه کنید

Configuration functions

اطلاعاتی در مورد تنظیمات کنونی اپشن ها ارائه میدهند :
@@LANGUAGE
نمایش زبانی که هم اکنون استفاده میشود
استفاده از یک فانکشن بشکل ذیل می باشد.
select @@LANGUAGE

@@SERVERNAME
نام سروری که اس کیوال سرور در آن درحال اجراست
select @@SERVERNAME

@@VERSION
اطلاعات در مورد نسخه اس کیوال سروری که به آن متصل هستیم
select @@VERSION

Conversion Functions (مهم)

توابع تبدیل نوع داده.
بوسیله این توابع میتوانیم datatype های خاصی را تحت شرایطی به یکدیگر تبدیل کنیم .
نکته : هر datatype ی قابل تبدیل به هر datatype ی نیست .

CAST and CONVERT

Syntax for CAST:
CAST ( expression AS data_type [ ( length ) ] )

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Expression : هر عبارت معتبر
data_type : datatype مقصد که می خواهیم تبدیل به آن صورت بگیرد
Length : عددی integer که طول Datatype مقصد را مشخص میکند اگر ذکر نشود پیشفرض ان 30 می باشد
Style : مقداری عددی که مشخص میکند تابع convert چگونه یک عبارت را ترجمه کند

کوئری بنویسید که نام و نام خانوادگی هر دانش اموز را با سن دانش آموز در یک جمله قرار دهد .
مثلا "محمد رحیمی 12 ساله"
select FirstName + ' ' + LastName + ' '+ age + ' ساله ' from Student
به خطا میخریم بنابراین از convert استفاده میکنیم
select FirstName + ' ' + LastName + ' '+convert(nvarchar(50),age) + ' ساله ' from Student
همین مثال با cast
select FirstName + ' ' + LastName + ' '+ cast(age as nvarchar(50)) + ' ساله ' from Student
برای ادامه تمرینات یک ستون دید به جدول Student می افزاییم
alter table Student
add LastAvg nvarchar(50)
حالا چند رکورد را Update میکینم
update Student set lastAvg='12.35' where id=1;
update Student set lastAvg='14.15' where id=2;
update Student set lastAvg='11.69' where id=3;
update Student set lastAvg='18.35' where id=4;
update Student set lastAvg='19.45' where id=5;
update Student set lastAvg='15.15' where id=6;
update Student set lastAvg='16.75' where id=7;


select sum(lastavg) from student
سعی می کنیم به عدد صحیح cast کنیم
select sum(cast(lastavg as int )) from student
حالا به دسیمال تبدیل میکنیم
select sum(cast(lastavg as decimal)) from student
تعداد رقم اعشار بصورت پیشفرض در دسیمال صفر است پس عدد بدون قسمت اعشاری آن جمع میشود و صحیح ان بشکل ذیل است
select sum(cast(lastavg as decimal(6,2))) from student
لیست دانش آموزانی را نمایش دهید که فیلد age آنها حاوی 2 باشد ؟
select FirstName,LastName,age from Student where cast(age as nvarchar(50)) like '%2%'

Date and Time Functions

توابع تاریخ و زمان
تمامی مقادیر بازگشتی توسط این توابع از تنظیمات سیستم عاملی که instance اس کیو ال سرور بروی آن نصب است . حاصل میشوند

()getdate تاریخ و زمان سرور

select getdate()

() DAY : عدد صحیحی بازمیگرداند که نماینده بخش روز از یک تاریخ است

()Month : بخش ماه

()Year : بخش سال

() DateAdd

اضافه کردن یک بازه زمانی خاص به یک تاریخ مثلا اضافه کردن 2 روز یا 3 ماه یا 3 سال به یک تاریخ (میلادی)

DATEADD (datepart , number , date )

Datepart : year , month,day,day,week,second,minute,hour

SELECT DATEADD(month, 1, '2006-08-30');
SELECT DATEADD(year, 1, '2006-08-31');
SELECT DATEADD(day, 24, '2006-12-08');

Logical Functions

انجام عملیات منطقی :

CHOOSE

: از میان ایستی از مقادیر ایتمی را با ایندکس مورد نظر بازمیگرداند (ایتم n ام)

CHOOSE ( index, val_1, val_2 [, val_n ] )

Index : موقعیت ایتم در لیست
مثلا :
SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;

نکته : index در اس کیوال سرور از 1 شروع میشود
اگر یک مجموعه داشته باشیم اولین آن دارای ایندکی 1 است برخلاف C# و VB.NET

IIF

یکی از دو مقدار موجود را باتوجه به صحیح بودن و یا غلط بوده یک عبارت منطقی باز میگرداند

IIF ( boolean_expression, true_value, false_value )

select iif(ContinuingEducation=1,'ادامه تحصیل','ترک تحصیل') from Student

Mathematical Functions

انجام محاسبات ریاضی
ABS , COS,SIN,TAN,SQRT,…
Round
مقدار عددی گرد شده به تعداد رقم مورد نظر را باز میگرداند.

ROUND ( numeric_expression , length [ ,function ] )

select round(1234242287.3456,2)
SELECT ROUND(150.75, 0);
SELECT ROUND(150.75, 0, 1);
با تنظیم پارامتر دلخواه سوم می توان کاری کرد که بجای گرد شدن .تعداد رقم اعشار مورد نظر حذف شود.
RAND
مقدار تصادفی بین 0 تا 1 ایجاد میکند

RAND ( [ seed ] )

Seed : پارامتر غیر اجباریست (optional) که عدد نخستینی که تولید مقادیر تصادفی از آن شروع میشوند را تعیین میکند و در صورت عدم تخصیص خود اس کیو ال سرور عددی را بطور تصادفی انتخاب میکند. در صورت تخصیص seed با هر بار اجرای این تابع همراه یک عدد باز گردانده خواهد شد.
SELECT CAST(RAND() * 10 AS INT) AS [RandomNumber]
اعداد تصادفی بین 0 تا 10
برای اینکه اعداد تصادفی بین دو مقدار را ایجاد کنیم
ROUND(((@Upper - @Lower ) * RAND() + @Lower), 0)
کوئری بنویسید که بین دانش اموزان کلاس . هر با دانش آموزی را به قید قرعه انتخاب کند
select ID,FirstName,LastName from Student where id in
((select ROUND(((6) * RAND()+1 ), 0))

Metadata Functions

این توابع اطلاعاتی درباره دیتابیس و ابجکتهای ان بازمیگردانند
Col_length()
طول تعریف شده یک ستون را باز میگرداندبه بایت

COL_LENGTH ( 'table' , 'column' )

select COL_LENGTH('Student','id')
select COL_LENGTH('Student','FirstName')

Security Functions

اطلاعاتی را باز میگردانند که برای مدیریت مسائل امنیتی کاربرد دارد
SELECT USER_NAME();

String Functions

توابع کار بروی رشته ها
CHAR()
تبدیل یک کد اسکی به یک کاراکتر

CHAR ( integer_expression )

integer_expression : مقداری بین 0 تا 255 را بخود میتواند بگیرد
select char(80)
select char(81) + char(13) + char(85)
char(13) خط جدید . همانند اینکه در انتهای یک رشته کلید enter را بزنیم
نتایج در مد نمایش result to text واضح ترند
CharIndex ()
: بدنبال یک رشته در رشته دیگری میگرددو در صورت پیدا کردن موقعیت انرا باز میگرداند (بر مبنای 1) (1-based)

CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )

در صورت پیدا نشدن تابع 0 باز میگرداند.
select charindex('a','gfgh')
موقعیت کلمه “پنگ” را در فیلد TrainingCourses بازای همه رکورد ها بدست آورید
select charindex(N'پنگ', TrainingCourses ) from Student
Concat ()
رشته ای را باز میگرداند که حاصل بهم پیوستن دو یا چند رشته ورودی می باشد

CONCAT ( string_value1, string_value2 [, string_valueN ] )

نکته بسایر مهم : اگر یکی از رشته ها null باشد . تابع مقدار رشته خالی empty string را بجای آن جایگزین میکند. و خروجی کل null نمی شود .

select null+ 'alireza'
select concat(null, 'alireza')
هنگامی که از + استفاده میکنیم اگر یکی از رشته ها null باشد نتیجه کل null خواهد بود ولیکن concat این مشکا را ندارد .

FORMAT ()
جهت قالب بندی رشته ها . به فرمت خاص

FORMAT ( value, format [, culture ] )

select FORMAT(123456789,'N','fa-ir')
select FORMAT(123456789,'N','en-us')

select FORMAT(123456789,'C')
select FORMAT(123456789,'G')

select getdate()
select format(getdate(),'dd/mm/yyyy')


LEFT(), RIGHT(),SUBSTRING()
جدا کردن قسمتی از یک رشته به طول مورد نظر
Left از چپ
LEFT ( character_expression , integer_expression )

select left('با سلام به شما',2)
Right از راست
RIGHT ( character_expression , integer_expression )

select right('با سلام به شما',3)

SUBSTRING()
SUBSTRING ( expression ,start , length )

select substring('با سلام به شما',4,4)

LOWER() , UPPER()
رشته ایی را باز میگرداند که در آن تمامی کاراکتر ها به حروف کوچک و یا حروف بزرگ تبدیل شده اند LOWER ( character_expression )

select upper('How Are You')

select lower('How Are You')

LTRIM(),RTRIM()
رشته ایی را باز میگرداند که کاراکتر های space ابتدایی از سمت چپ و یا راست آن حذف شده است .

REPLACE()
جایگزینی یک رشته در رشته دیگر
REPLACE ( string_expression , string_pattern , string_replacement )

SELECT REPLACE('abcdefghicde','cde','YYY');

LEN()
طول رشته
LEN ( string_expression )

Space()
رشته ایی باز میگرداند که حاوی تعدادی space است (به تعداد پارامتر expression)
SPACE ( integer_expression )

select FirstName + space(5) + LastName from Student
select concat(firstname,space(5),LastName) from Student

System Functions

انجام عملیات و باز گرداندن اطلاعات در رابطه با مقادیر ، ابجکت ها و تنظیمات
@@IDENTITY
اخرین identity درج شده را باز میگرداند.
insert into Student (FirstName,LastName)
values (N'محمد', N'بسطامی')
select @@IDENTITY


ISNULL()
جایگزین کردن مقادیر null با مقادیر مشخص
ISNULL ( check_expression , replacement_value )

select FirstName+lastname+ TrainingCourses from Student
select FirstName+lastname+ isnull(TrainingCourses,'') from Student

ISNUMERIC()
بررسی اینکه آیا یک عبارت ، یک مقدار عددی معتبر است یا خیر
ISNUMERIC ( expression )

select ISNUMERIC('A')
select ISNUMERIC('1')
1 در صورت معتبر بودن و 0 در صورت عدم اعتبار
ISNUMERIC ( expression )

select ISNUMERIC([LastAvg]) from Student

NEWID ()
ایجاد یک مقدار منحصر بفرد از نوع uniqueidentifier
NEWID ( )

بسیاری از مفاهیم درفیلم اموزشی بطور مفصل شرح داده شده اند . لذا حتما فیلم آموزشی این فصل را ببینید .

مفاهیم و موضوعات این فصل عبارتند از :

• Aggregate functions

• Ranking function

• Configuration functions

• Conversion Functions

• Date and Time Functions

• Logical Functions

• Mathematical Functions

• Metadata Functions

• Security Functions

• String Functions

• System Functions