آموزشگاه برنامه نویسی تحلیل داده
آموزشگاه برنامه نویسی تحلیل داده

توابع پیش فرض SQL

دوره های مرتبط با این مقاله

مقدمه ای بر توابع پیش فرض (Built-In Functions)

برای آسان تر شدن فرایند طراحی و توسعه، زبان Transact-SQL با تعدادی تابع از پیش ساخته و تست شده عرضه می شود. کافی است با این توابع، ساختار دستوری و خروجی آن ها آشنا باشید.
برای راحتی و تسریع در بکاربردن و شناسایی توابع مناسب (متناسب با عملیات مورد نظر)، زبان T-SQL این دست از توابع را بر مبنای نوع و میزان کارایی آنها دسته بندی کرده است.
بخاطر وجود برخی از پیچیدگی ها، می توان برخی از مقادیر را به راحتی شناسایی کرده و ثابت تنظیم کرد. به عنوان مثال، تاریخی همچون 6 ژانویه 1995، مقداری ثابت است و هیچگاه تغییر نمی کند. این نوع مقدار از آن جایی که هیچگاه تغییر نمی کند، مقدار ثابت یا قطعی (deterministic) نامیده می شود. مقدار زمانی 5PM نیز مقداری ثابت بوده و تغییر نمی کند. در این میان برخی مقادیر هم از پیش برای ما شناخته شده نیستند و نیز بسته به شرایط تغییر می کنند. به طور مثال، تاریخ شروع مدارس ممکن است از سالی به سال دیگر متفاوت باشد، اما به طور قطع هر سال اتفاق می افتد. بدین معنی که شما می دانید این امر سرانجام رخ می دهد فقط از زمان دقیق آن آگاهی ندارید. چنین مقداری نامشخص یا غیر قطعی قلمداد می شود.
برای پشتیبانی از مفاهیم deterministic و non-deterministic، T-SQL دو دسته ی کلی از توابع ارائه می کند. تابعی که همیشه مقداری یکسان و شناخته شده را برمی گرداند، تابع deterministic تلقی می شود. تابعی که مقدار بازگشتی آن بسته به شرایط تغییر می کند، non-deterministic خوانده می شود.
کار را با پایگاه داده ی university ادامه می دهیم. در این نسخه از پایگاه داده ی خود، University4، یک جدول ویژه ی دانشجویان به پایگاه داده ی مورد نظر اضافه می کنیم. این جدول در schema ای با نام Academics ذخیره می گردد.


معرفی و استفاده از توابع از پیش ساخته

1. رایانه را راه اندازی کرده و وارد سیستم شوید.
2. محیط Microsoft SQL Server اجرا کرده و در کادر محاوره ای اتصال به سرور، دکمه ی Connect را کلیک کنید.
3. بر روی اسم سرور راست کلیک کرده و دکمه ی New Query را از نوار ابزار کلیک کنید.
4. جهت ایجاد یک پایگاه داده ی جدید، در پنجره ی ویرایش کوئری، دستورات زیر را درج نمایید:


USE master;
GO
DROP DATABASE University2;
GO
DROP DATABASE University3;
GO
CREATE DATABASE University4;
GO
USE University4;
GO
CREATE SCHEMA Academics;
GO

5. برای اجرای دستورات SQL، کلید F5 را فشار دهید.


تجزیه و PARSE یک مقدار یا عبارت

پروسه ی parse عبارت است از پویش و اسکن تمامی اجزای (همچون تمامی علامت ها) یک عبارت یا واژه به منظور تطبیق با یک الگوی مشخص. برای مثال، در parsing تمامی علامت های موجود در یک عبارت مورد بررسی قرار می گیرد تا مشخص شود آیا علامت مورد نظر یک عدد است یا هر چیز دیگر. بر این اساس، parse عبارتی نظیر 275.25 مشخص می کند آیا این مقدار عددی از نوع decimal است یا خیر.
برای تجزیه یک عبارت یا سهولت در فرایند parsing، زبان T-SQL یک تابع به نام PARSE را ارائه می دهد. ساختار دستوری آن به نحو زیر می باشد:


PARSE ( string_value AS data_type [ USING culture ] )

تابع ذکر شده یک آرگومان می گیرد. این تابع آرگومان را به صورت رشته می پذیرد، پس از آن کلیدواژه ی AS و سپس نوع داده ای مشخص می شود. PARSE() برای اسکن و پویش یک عبارت که به عنوان آرگومان string_value به آن ارسال شده، بکار می رود. عبارتی که به عنوان پارامتر به تابع فرستاده می شود باید از قوانین مربوط به آرگومان data_type پیروی کند. به عنوان مثال، می خواهیم دریابیم آیا نوع داده ای یک مقدار integer (عدد صحیح) هست یا خیر. در این صورت می توانید نوع داده ای (data_type) را در قالب و به صورت int به تابع ارسال کنید. عبارتی که به عنوان آرگومان به تابع ذکر شده فرستاده می شود باید فقط و فقط حاوی عدد باشد. حال اگر بخواهیم بررسی کنیم آیا مقداری از نوع date است یا خیر، بایستی نوع داده ای مقدار را date یا datetime2 تعریف کرده، سپس آن را به عنوان مقدار به تابع ارسال کرد. در خصوص نوع داده ای date، مقدار می بایست از قوانین مربوط به نوع داده ی date پیروی کند. برای مشاهده ی این قوانین باید به تب Date ← Customize Regional Options که از طریق Regional and Language Options کنترل پنل قابل دسترسی می باشد، مراجعه کرد.
چنانچه آرگومان دربردارنده ی کاراکترهای بین المللی (Unicode) باشد، در آن صورت لازم است زبان آن را که در اصطلاح culture خوانده می شود و در انتهای آرگومان می آید، مشخص کنید.
چنانچه تابع PARSE() قادر به شناسایی نوع داده ای نیست و یا مقدار آرگومان از قوانین مربوط به data_type پیروی نمی کند، در آن صورت تابع نام برده یک خطا صادر می کند. جهت حل این مسئله، زبان T-SQL تابع TRY_PARSE() را به عنوان جایگزینی برای PARSE() ارائه می کند. ساختار نگارشی آن:


TRY_PARSE ( string_value AS data_type [ USING culture ] )

همان طور که مشاهده می کنید، این تابع همان آرگومان ارسالی به PARSE() را می پذیرد. تفاوتی که میان دو تابع مذکور وجود دارد این است که PARSE() در صورت شکست خوردن عملیات تجزیه یک خطا صادر می کند، در حالی که TRY_PARSE() (در صورت عدم موفقیت فرایند تجزیه) مقدار NULL را برمی گرداند. از این رو توصیه می کنیم بجای PARSE() اغلب از تابع TRY_PARSE() استفاده کنید.


تبدیل یک مقدار (cast)

در بیشتر مواقع، مقداری که کاربر به پایگاه داده ارائه می کند، از نوع رشته است. چنانچه مقدار مورد انتظار شما نیز رشته باشد، در آن صورت هیچ مشکلی پیش نمی آید. اما اگر مقداری که کاربر فراهم می کند از نوعی غیر از رشته است، برای مثال کاربر به عنوان مقدار یک عدد ارائه می کند، در آن صورت پیش از بکار بردن مقدار بایستی آن را به نوع مورد نیاز تبدیل کرد. بدین معنی که می بایست آن را از رشته به نوع داده ای مورد انتظار تبدیل نمود.
برای این منظور (تبدیل مقدار)، زبان T-SQL تابع CAST() را فراهم می کند. نحوه ی استفاده از آن به ترتیب زیر می باشد:


CAST ( Expression AS DataType [ ( length ) ] )

آرگومان Expression آن مقداری است که باید تبدیل شود. DataType نوع داده ای است که می خواهید مقدار آرگومان Expression به آن تبدل شود. DataType می تواند تمامی انواع داده ای که در درس 4 به آن پرداختیم، باشد.
چنانچه تابع مزبور موفق به تبدیل مقدار نشد، یک پیام خطا صادر خواهد کرد. T-SQL یک تابع دیگری به نام TRY_CAST ارائه می کند که می تواند جایگزینی برای CAST باشد (در صورت کارگر نبودن cast می توان از try-cast کمک گرفت). نحوه ی کلی استفاده از آن به صورت زیر می باشد:


TRY_CAST ( expression AS data_type [ ( length ) ] )

این تابع همان آرگومان های CAST را می پذیرد. تفاوت میان دو تابع یاد شده این است که TRY_CAST() در صورت عدم موفقیت در تبدیل، مقدار NULL را برمی گرداند. توصیه می شود بجای CAST() از TRY_CAST() استفاده کنید.
1. در داخل پنجره ی ویرایش کوئری (Query Editor)، کلیک کرده و بعد کلیدهای Ctrl + A را همزمان فشار دهید تا کلیه ی دستورات انتخاب شود.
2. حال دستورات زیر را وارد نمایید:


 DECLARE @StrSalary nvarchar(10),
            @StrHours  nvarchar(6),
            @WeeklySalary decimal(6,2)
            SET @StrSalary = N'2W2.18';
            SET @StrHours  = N'38.50';

            SET @WeeklySalary = CAST(@StrSalary As Decimal(6,2)) *
            CAST(@StrHours As Decimal(6,2));
            SELECT @WeeklySalary;
            GO

3. با زدن کلید F5، مجموعه دستورات را اجرا نمایید.


آموزش SQL

4. برای جلوگیری از رخداد این خطا، دستورات را به صورت زیر ویرایش کنید:


DECLARE @StrSalary nvarchar(10),
	@StrHours  nvarchar(6),
	@WeeklySalary decimal(6,2)
SET @StrSalary = N'2W2.18';
SET @StrHours  = N'38.50';
SET @WeeklySalary = TRY_CAST(@StrSalary As Decimal(6,2)) *
		    CAST(@StrHours As Decimal(6,2));
SELECT @WeeklySalary;
GO

5. کلید F5 را برای اجرای دستورات فشار دهید.


آموزش SQL

6. بار دیگر دستورات را به ترتیب زیر ویرایش می کنیم:


DECLARE @StrSalary nvarchar(10),
	@StrHours nvarchar(6),
	@WeeklySalary decimal(6,2)
SET @StrSalary = N'242.18';
SET @StrHours  = N'38.50';
SET @WeeklySalary = TRY_CAST(@StrSalary As Decimal(6,2)) *
	            TRY_CAST(@StrHours As Decimal(6,2));
SELECT @WeeklySalary;
GO

7. دستورات را با F5 اجرا نمایید.


آموزش SQL

تبدیل یک مقدار (Convert)

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


CONVERT(DataType [ ( length ) ] , Expression [ , style ])

اولین آرگومان باید یک نوع داده ای تعریف شده (انواع داده ای که پیش تر معریف کردیم) باشد. چنانچه مقداری را به نوع داده ای رشته (varchar، nvarchar، char، nchar) یا باینری تبدیل می کنید، بایستی حداکثر تعداد کاراکترهای مجاز را نیز در پرانتز مقابل نوع داده ای یا data type (در جای آرگومان length) مشخص نمایید.
همان گونه که در خصوص تابع CAST() تشریح شد، آرگومان Expression مقداری است که می بایست تبدیل شود (در جای آرگومان Expression مقداری که باید تبدیل شود را قرار دهید).
اگر عملیات تبدیل بر روی مقداری از نوع date و time اجرا شود، در آن صورت آرگومان style (که درواقع یک عدد می باشد) مشخص می کند تبدیل چگونه صورت گرفته یا ادامه یابد.
تابع CONVERT() در صورت عدم موفقیت در تبدیل، یک خطا صادر می کند.
به دلیل وجود برخی از مشکلات که ممکن است در اثر فرایند تبدیل رخ دهد و برطرف ساختن آن ها، T-SQL تابعی به نام TRY_CONVERT را ارائه می دهد. این تابع می تواند جایگزینی برای CONVERT() باشد. نحوه ی کلی استفاده از آن به شرح زیر می باشد:


TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

آرگومان ها بین دو تابع تبدیل نوع یکسان می باشد. تنها تفاوت موجود این است که در صورت عدم موفقیت در تبدیل،TRY_CONVERT() بجای صدور خطا، مقدار NULL را برمی گرداند.


توابع Macroو Metadata

Macro یک عملیات است که بر پایگاه داده ی یا بخش های خاصی از آن و یا database server اجرا می شود. Macro شباهت هایی به تابع دارد اما از نظر عملکرد یکی نیستند. زبان T-SQL تعداد متعددی macro ارائه می کند که در مدیریت پایگاه داده و اشیا آن کمک قابل توجهی به شما می کند.


بررسی وجود و پیدا کردن یک رکورد در جدول

یکی از ساده ترین عملیاتی که کاربر می تواند بر روی جدول پایگاه داده اجرا کند، جستجو و یافتن رکورد مورد نظر است. برای این منظور، کاربر جدول دربردارنده ی رکوردها را باز کرده و آن را به صورت دیداری یا ویژوال بازبینی می کند، سپس به دنبال فیلدی حاوی اطلاعات مثلا اسم دانش آموز می گردد.
به عنوان یک توسعه دهنده و برنامه نویس پایگاه داده، شما نیز می توانید رکورد مورد نظر را جستجو کنید. برای این منظور، چندین روش مختلف وجود دارد. برای یافتن رکورد مد نظر، T-SQL یک macro به نام EXISTS ارائه می کند. نحوه ی استفاده از آن به شرح زیر می باشد:


BIT EXISTS(SELECT Something)

این macro یک آرگومان می گیرد. پارامتر ورودی بایستی یک دستور SELECT باشد که برای بازگردانی مقداری که موجودیت آن بررسی می شود، بکار می رود. به عنوان مثال، از وجود یک system database به نام databases که دربردارنده ی رکوردی از تمامی پایگاه داده های ذخیره شده و مستقر بر روی server است، آگاهی داریم. شما می توانید با استفاده از ماکرو EXISTS()، وجود یا عدم وجود پایگاه داده ی معین را بررسی کنید.


مقدمه ای بر توابع Metadata

اطلاعاتی که یک شی دربردارد Metadata خوانده می شود. T-SQL توابع متعددی دارد که با بهره گیری ازmetadata اطلاعاتی را درباره ی اشیا یک پایگاه داده یا سرویس دهنده ای که پایگاه داده بر روی آن مستقر است، ارائه می دهد.


دسترسی به شناسه (id) یک شی

یک پایگاه داده به طور کلی دربردارنده ی انواع مختلف اشیا همچون جدول می باشد. زمانی که یک شی جدید در بانک اطلاعاتی ایجاد می کنید، همزمان با آن موتور db به طور خودکار یک شناسه ی داخلی و منحصر بفرد برای شی مورد نظر ایجاد می کند (سپس آن اسم یا شناسه را در جایی ذخیره می کند). این کار به موتور db اجازه می دهد تمامی اشیا ذخیره شده در کلیه ی پایگاه های داده را شناسایی کند. T-SQL با فراهم نمودن تابعی به نام OBJECT_ID، شناسه ی شی مورد نظر را برمی گرداند. نحوه ی کلی استفاده از آن به صورت زیر می باشد:


OBJECT_ID('[ database_name . [ schema_name ] . | schema_name . ] 
 	  object_name' [ ,'object_type' ]);

تابع یاد شده دو آرگومان می گیرد که به عنوان (در قالب) رشته به آن ارسال شده اند. اولین آرگومان الزامی و دومین آرگومان ارسالی به تابع اختیاری می باشد. اولین آرگومان، object_name، اسم شی مورد نظر (مثلا جدول) را مشخص می کند.


مثال:
SELECT COLUMNPROPERTY(OBJECT_ID(N'Employees'),
      		      N'LastName',
      		      N'precision')AS [Maximum Number of Characters];
GO

چنانچه شی مورد نظر به schema ای غیر از dbo تعلق دارد، در آن صورت می بایست پیش از اسم شی، اسم schema را ذکر کنید. همچنین می توانید با درج اسم پایگاه پیش از schema، تعریف اسم شی مورد نظر را تکمیل نمایید.
دومین آرگومان تابع OBJECT_ID() به شما اجازه می دهد نوع شی را مشخص کنید. این آرگومان، بسته به شی مورد نظر، در قالب یک یا دو حرف به تابع فرستاده می شود، برای مثال چنانچه شی مورد نظر یک جدول می باشد، حرف 'U' به عنوان آرگومان به تابع OBJECT_ID() ارسال می شود.


شناسه ی پایگاه داده

همان طور که در درس دو این سری آموزشی ذکر شد، هر بار که پایگاه داده ی جدیدی ایجاد می شود، پایگاه داده ی مورد نظر به system database ای که master نام دارد اضافه می گردد. پایگاه داده ی تازه ایجاد شده توسط sys.databases قابل دسترسی (مکان یابی) می باشد. همچنین هر بار که پایگاه داده ی جدیدی به پایگاه داده ی master اضافه می گردد، به طور پیش فرض یک عدد صحیح (integer) به آن تخصیص می یابد که شناسه ی منحصربفرد پایگاه داده ی مورد نظر قلمداد شده و نیز آن را از دیگر دیتابیس ها تمییز می دهد.
برای دسترسی به شناسه ی یک پایگاه داده، می توانید تابع DB_ID() را فراخوانی کنید. دستور نحوی آن به شرح زیر می باشد:


DB_ID ( [ 'database_name' ] ) RETURNS int;

تابع نام برده یک آرگومان می گیرد که اختیاری می باشد. در صورتی که تابع را بدون ارسال آرگومان به آن صدا بزنید، اسم پایگاه داده ی جاری (پایگاه داده ای که هم اکنون انتخاب شده) را برمی گرداند


مثال:
SELECT DB_ID() AS [Selected Database];
GO

برای دسترسی به شناسه ی پایگاه داده ی موجود، اسم آن را به عنوان آرگومان به تابع ارسال کنید.


مثال:
SELECT DB_ID(N'KoloBank') AS [ID For Kolo Bank];
GO

در حقیقت، هنگامی که یک آرگومان به تابع DB_ID() پاس می دهید، متور db به دنبال پایگاه داده ای با آن اسم می گردد. در صورت یافت شدن پایگاه داده ای با آن اسم، تابع عدد صحیح که شناسه ی منحصر بفرد آن می باشد را بازیابی می کند. حال چنانچه دیتابیسی به آن نام یا شناسه یافت نشد، تابع مزبور مقدار NULL را برمی گرداند (ولی خطا صادر نمی کند).


بازیابی پایگاه داده ی جاری

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


DB_NAME ( [ database_id ] ) RETURNS nvarchar(128);

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


SELECT DB_NAME();
GO

در صورت تمایل می توان یک integer (عدد صحیح) به تابع ارسال کنید که بیانگر اندیس پایگاه داده می باشد. در زیر جدولی را مشاهده می کنید که پایگاه داده ی برگشتی را در قبال ارسال آرگومان های فهرست شده در این جدول به نمایش می گذارد:


تابع مورد نظر این پایگاه داده را برمی گرداند
در صورت ارسال این آرگومان
master
1
tempdb
2
model
3
msdb
4
ReportServer
5
ReportServerTempDB
6

این اعداد که به عنوان آرگومان به تابع ارسال می شوند، به موتور db دستور می دهد آن پایگاه داده ای که با شماره ی آرگومان متناظر می باشد را بازیابی کند. در واقع می توانید تابع DB_ID را صدا زده و آن را به عنوان آرگومان به تابع DB_NAME ارسال نمایید.


مثال:
SELECT DB_NAME(DB_ID(N'KoloBank')) AS [Kolo Bank];
GO

بازیابی شناسه ی کاربر جاری

در T-SQL چندین تابع از پیش تعریف شده برای بازیابی اسم کاربر جاری (username ای که کاربر با آن در حال حاضر به سیستم وارد شده/log on کرده) در نظر گرفته شده است. برای بدست آوردن شناسه ی کاربری، می توانید تابع SUSER_ID یا SUSER_SID را فراخوانی نمایید. دستور گرامری و نحوه ی استفاده از آن به صورت زیر می باشد:


SUSER_ID ( [ 'login' ] ) RETURNS int;
SUSER_SID ( [ 'login' ] ) RETURNS varbinary(85);

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


آموزش SQL

برای بدست آوردن اسم حقیقی کاربر، باید تابع SUSER_NAME یا SUSER_SNAME را صدا زد. نحوه ی کلی استفاده از آن به ترتیب زیر می باشد:


SUSER_NAME ( [ server_user_id ] ) RETURNS nvarchar(128);
SUSER_SNAME ( [ server_user_sid ] ) RETURNS nvarchar(128)

بازیابی اسم رایانه ی مورد استفاده

برای بدست آوردن اسم رایانه ی مورد استفاده، می بایست تابع HOST_NAME() را فراخوانی نمود. سینتکس:


HOST_NAME() RETURNS nvarchar(128);

مثال:
/articles.tahlildadeh.com/image.axd?picture=clip_image005_31.gif alt="آموزش SQL">

مبانی توابع مبتنی بر رشته

رشته یا string اولیه و اساسی ترین مقداری است که در پایگاه داده مورد استفاده قرار می گیرد. دلیلش این است که هر مقداری، پیش از اینکه مورد پردازش قرار گیرد، در ابتدای امر به طور پیش فرض یک رشته درنظر گرفته می شود. در یک برنامه راه و روش های مختلفی برای استفاده از رشته و بازیابی آن وجود دارد. به عنوان مثال، می توان آن را بازیابی کرده و یا آن را به صورت یک رشته ی با مقدار ثابت (رشته ای که مقدارش ثابت بوده و از پیش برای شما مشخص می باشد و نیز همیشه با همین مقدار به تابع ارسال می شود) به تابع فرستاد. همچنین می توانید یک رشته را که کاربر فراهم می کند، مورد استفاده قرار دهید. توابع نیز می توانند یک رشته تولید کرده یا برگردانند.
برای سهولت در مدیریت رشته و اجرای عملیات مورد نیاز بر روی آن ها، T-SQL مجموعه ای از توابع را عرضه می کند. این توابع به چندین دسته تقسیم می شوند: توابع character-based یا مبتنی بر کاراکتر، توابعی که برای تبدیل بکار می روند (conversion)، توابع اتصال، sub-string (توابعی که یک زیر رشته برمی گردانند).


بازیابی طول یک رشته

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


int LEN(String)

این تابع یک آرگومان به عنوان رشته می گیرد، سپس در خروجی تعداد کاراکترهای موجود در آن رشته را برمی گرداند.


مثال:
DECLARE @FIFA nvarchar(120)
SET @FIFA = N'Fédération Internationale de Football Association'
SELECT @FIFA AS FIFA
SELECT LEN(@FIFA) AS [Number of Characters]

نتیجه ی زیر را بدست می دهد:


آموزش SQL

اتصال دو رشته

متصل کردن (concatenate) دو رشته، عبارت است از پیوند زدن یک رشته به رشته ی دیگر. این کار به وسیله ی عملگر '+' امکان پذیر می باشد.


مثال:
DECLARE @FirstName nvarchar(24),
        @LastName nvarchar(24),
	@FullName nvarchar(50),
	@CompleteName nvarchar(50);
SET @FirstName = N'Jerome ';
SET @LastName = N'Ansfield';
SET @FullName = @FirstName + @LastName;
SET @CompleteName = N'Anne ' + N'Sanders';
SELECT N'Jeffrey ' + N'Erschbamer'; 
SELECT @FullName [Full Name];
SELECT @CompleteName [Complete Name];
GO

نتیجه ی زیر حاصل می گردد:


آموزش SQL

به همین ترتیب، می توانید چندین رشته را با درج عملگر جمع بین آن ها به هم متصل کرد.


مثال:
DECLARE @FirstName nvarchar(24),
	@LastName nvarchar(24),
	@FullName nvarchar(50),
	@CompleteName nvarchar(50);
SET @FirstName = N'Jerome';
SET @LastName = N'Ansfield';
SET @FullName = @LastName + N', ' + @FirstName;
SET @CompleteName = N'Anne' + N' ' + N'Juliette' + N' ' + N'Sanders';
SELECT @FullName [Full Name];
SELECT @CompleteName [Complete Name];
SELECT N'Jeffrey' + N' ' + N'Anselme' + N' ' + N'Erschbamer'; 
GO

خروجی زیر را تولید می کند:


آموزش SQL

Transact-SQL یک تابع ویژه ی به هم پیوند زدن رشته ها در اختیار توسعه دهنده قرار می دهد که آن CONCAT می باشد. دستور استفاده از آن به صورت زیر می باشد:


CONCAT(string string_value1,
       string string_value2
       [, string string_value_N]) RETURNS string;

تابع ذکر شده می تواند تعداد بیشماری آرگومان ورودی داشته باشد، بدین معنی که می توان تابع مورد نظر را برای اتصال رشته های متعدد بکار برد. خروجی این تابع یک رشته خواهد بود. در این فرمول، string را به عنوان یک نوع داده ای بکار بردیم. هر یک از آرگومان های ورودی می تواند از نوع char یا مشتقات آن (nchar، char(n)، varchar،nvarchar(n)، nvarchar(max) ) باشد.
در زیر مثال هایی را مشاهده می کنید که در آن دو رشته توسط تابع نام برده به هم متصل می شوند:

DECLARE @FirstName nvarchar(24),
        @LastName nvarchar(24),
	@FullName nvarchar(50),
	@CompleteName nvarchar(50);
SET @FirstName = N'Jerome ';
SET @LastName = N'Ansfield';
SET @FullName = CONCAT(@FirstName, @LastName);
SET @CompleteName = CONCAT(N'Anne ', N'Sanders');
SELECT CONCAT(N'Jeffrey ', N'Erschbamer'); 
SELECT @FullName [Full Name];
SELECT @CompleteName [Complete Name];
GO

حال مثالی را مشاهده می کنیم که این تابع بیش از دو آرگومان پذیرفته:


DECLARE @FirstName nvarchar(24),
	@LastName nvarchar(24),
	@FullName nvarchar(50),
	@CompleteName nvarchar(50);
SET @FirstName = N'Jerome';
SET @LastName = N'Ansfield';
SET @FullName = CONCAT(@LastName, N', ', @FirstName);
SET @CompleteName = CONCAT(N'Anne', N' ', N'Juliette', N' ', N'Sanders');
SELECT @FullName [Full Name];
SELECT @CompleteName [Complete Name];
SELECT CONCAT(N'Jeffrey', N' ', N'Anselme', N' ', N'Erschbamer'); 
GO

آموزش کاربردی: پیوند زدن دو ستون

1. داخل پنجره ی Query Editor کلیک کرده و کلیدهای Ctrl + A را همزمان فشار دهید تا کد موجود در پنجره انتخاب شود.
2. به منظور ایجاد جدول جدید، مجموعه دستورات زیر را در آن وارد نمایید:


USE University4;
GO
CREATE TABLE Academics.UndergraduateStudents
(
    StudentNumber nvarchar(10) not null,
    FirstName nvarchar(20),
    MiddleName nvarchar(20),
    LastName nvarchar(20),
    EmployeeName AS CONCAT(LastName, N', ', FirstName),
    Username nvarchar(12),
    [Password] nvarchar(20) DEFAULT N'Password1',
    Gender nvarchar(20)
);
GO

3. به منظور اجرای دستورات SQL، کلید F5 را فشار دهید.


رشته و تبدیل نوع: فرمت دهی یک مقدار

در Transact-SQL و Microsoft Windows، هر مقداری از هر نوع، در فرمتی خاص که از پیش تعریف شده نمایش داده می شود. اما گاهی می خواهیم مقدار مورد نظر به شکل یا در قالب دیگری نمایش داده شود. برای این منظور، T-SQL تابع FORMAT() را از بستر اجرای نرم افزار .NET وام می گیرد. تابع FORMAT برای قالب دهی به یک فیلد (مشخص نمودن نحوه ی نمایش یک فیلد یا ستون) استفاده می شود. نحوه ی بکارگیری آن:


FORMAT(Type value, nvarchar format [, nvarchar culture ] ) RETURNS nvarchar

تابع نام برده به شما این امکان را می دهد تا نحوه ی قالب بندی و نمایش مقدار به کاربر را مدیریت نمایید. این تابع را می توان به مقادیر با هر نوع داده ای نظیر integer، floating-point، date یا time و مشتقات آن ها اعمال کرد. اولین آرگومان به نوع مقدار مورد استفاده بستگی دارد.بار دیگر یادآور می شویم که این مقدار می تواند از هر نوعی باشد (date/time، number).
دومین آرگومان فرمتی که می بایست به آرگومان اول اعمال شود را مشخص می کند. استفاده از سومین آرگومان کاملا اختیاری است. لازم است چند مسئله ی مهم را به خاطر داشته باشید:
1. دومین آرگومان بایستی به صورت رشته به تابع پاس داده شود.
2. فرمتی که در آرگومان دوم مشخص می شود به نوع مقدار آرگومان اول بستگی دارد.
3. فرمت آرگومان دوم باید از اصول و قوانین زبانی که برای آن بکار می رود پیروی کند. اما از آنجایی که این تابع در تفسیر آرگومان دوم انعطاف پذیر می باشد، بدین معنی که در انتخاب فرمت دست شما را باز می گذارد، می توانید فرمتی بکار ببرید که برای کاربر به راحتی قابل فهم باشد.
عمدتا نوع داده هایی که بکار برده می شود و از دیگر انواع کاربرد بیشتری دارد، اعداد طبیعی هستند. مثال


مثال:
DECLARE @YearlySalary int,
        @FiscalBudget bigint;
SET @YearlySalary = 48626;
SET @FiscalBudget = 12640685;
SELECT @YearlySalary AS "Yearly Salary",
       @FiscalBudget AS "Company Budget";
GO

اگر بخواهیم مقدار مد نظر را در قالب اعداد در مبنای 16 (hexadecimal) نمایش دهیم، چه؟ در زبان انگلیسی آمریکایی، زمانی که عددی بالای 1000 می باشد، می توانید هر سه عدد را به وسیله ی ویرگول از هم جدا کنید. برای نمایش این حالت می توان از تابع FORMAT() کمک گرفت. برای این منظور مقدار مورد نظر را در جای آرگومان دوم قرار داده و این آرگومان را به صورت:
1. G یا g ارسال کنید: این فرمت که درواقع مخفف general می باشد، از موتور db درخواست می کند که از همان روش پیش فرض و تعریف شده در SQL(همان طور که در کتابخانه ی داخلی SQL تنظیم شده) برای اشاره به نوع داده ای متغیر و ستون استفاده کند. در بیشتر موارد عدد مورد نظر به همان گونه ای که به متغیر تخصیص یافته یا برای ستون تنظیم شده، نمایش داده می شود.

مثال:
آموزش SQL

2. D یا d را به عنوان آرگومان دوم پاس دهید: این آرگومان برای اعداد طبیعی بکار می رود. متغیر مورد نظر بایستی با نوع داده ای int اعلان شده باشد و یا نوع داده ی ستون بر روی نوع integer تنظیم شده باشد. در این حالت تابع ذکر شده عدد را بدون بخش کسری یا اعشاری نمایش خواهد داد.


مثال:
DECLARE @HourlySalary int,
        @Distance real;
SET @HourlySalary = 28.65;
SET @Distance = 146.704;
SELECT FORMAT(@HourlySalary, N'D') AS "Hourly Salary",
       FORMAT(@Distance, N'D') AS Distance;
GO

آموزش SQL

3. X یا x: عدد hexadecimal، یک مقدار عدد صحیح یا integer است که از حروف A تا F و اعداد تشکیل می شود. متغیر یا ستون مورد نظر بایستی از نوع int باشد. دیگر نوع های داده باعث تولید مقدار NULL می شود. برای این منظور کافی است X را به عنوان آرگومان دوم به تابع ارسال کنید.


مثال:
آموزش SQL

4. F یا f: عددی که با تعداد مشخص و ثابتی ارقام در بخش اعشاری نمایش داده شود، fixed خوانده می شود. به صورت پیش فرض، این عدد (دقت اعشاری) بر روی 2 تنظیم شده است.


مثال:
آموزش SQL

5. P یا p: یکی از راه های نمایش بخش کسری به صورت درصد می باشد. برای پشتیبانی از این امکان، تابع FORMAT() می تواند از فرمت P استفاده کند. به هنگام استفاده از این فرمت، نوع داده ای متغیر یا فیلد مورد نظر و نیز نحوه ی تخصیص عدد به متغیر یا ستون بسیار حائز اهمیت می باشد. به عنوان مثال، برای اینکه عددی را با دو رقم اعشار (به صورت درصد)نمایش دهیم، مقدار بایستی به شکل 0.X باشد که همان صفر و به دنبال آن نقطه و سپس هر عدد دیگری است.


مثال:
DECLARE @DiscountRate int,
        @RateOfDiscount int,
        @DiscountPart real,
        @InterestRate decimal(8, 4);
SET @DiscountRate = 25.50;
SET @RateOfDiscount = 0.2550;
SET @DiscountPart = 0.2550;
SET @InterestRate = 0.1295181;
SELECT FORMAT(@DiscountRate, N'P') "Discount Rate",
       FORMAT(@RateOfDiscount, N'P') "Discount Rate",
       FORMAT(@DiscountPart, N'P') "Discount Rate",
       FORMAT(@InterestRate, N'p') N'Interest Rate';
GO

آموزش SQL

6. N یا n: اغلب زبان ها، از جمله انگلیسی آمریکایی، با کنار هم قرار دادن و گروه بندی ارقام، مقادیر عددی بسیار بزرگی را ایجاد می کنند. یکی از روش های گروه بندی ارقام، قرار دادن آن ها در دسته های سه تایی می باشد. نمونه هایی از بکار گیری فرمت n یا N را در زیر مشاهده می کنید:


آموزش SQL

7. E یا e: این آرگومان (فرمت) برای نشان دادن بخش نمایی (توانی) یک عدد بکار می رود، به عنوان مثال می توان به X.XXXXXXE+00X اشاره کرد. هر x بیانگر یک عدد می باشد. عملگر نقطه، حرف E و علامت + یا – در این ترکیب قابل استفاده می باشند.


مثال:
DECLARE @HourlySalary int,
        @UnitPrice decimal(8, 4),
        @FiscalBudget bigint;
SET @HourlySalary = 28.65;
SET @UnitPrice = 349.95;
SET @FiscalBudget = 12640685;
SELECT FORMAT(@HourlySalary, N'E') AS "Hourly Salary",
       FORMAT(@UnitPrice, N'e') [Unit Price],
       FORMAT(@FiscalBudget, N'E') AS N'Company Budget';
GO

آموزش SQL

8. C یا c: این فرمت برای نمایش مقادیر ارزی بکار می رود. در زبان انگلیسی آمریکایی، پیش از عدد مورد نظر علامت $ درج می گردد:


1. در صورتی که مقدار عددی مورد نظر دربردارنده ی بخش اعشاری (عدد طبیعی یا ممیز شناور) باشد، کسر 00. به آن عدد اضافه می شود.
2. چنانچه عدد بزرگتر از عدد 999 باشد، هر سه عدد توسط تفکیک گر ویرگول از هم جدا می شوند.


مثال:
DECLARE @HourlySalary int,
        @UnitPrice decimal(8, 4),
        @FiscalBudget bigint;
SET @HourlySalary = 28.65;
SET @UnitPrice = 349.95;
SET @FiscalBudget = 12640685;
SELECT FORMAT(@HourlySalary, N'C') AS "Hourly Salary",
       FORMAT(@UnitPrice, N'C') [Unit Price],
       FORMAT(@FiscalBudget, N'c') AS N'Company Budget';
GO

آموزش SQL

9. اگر حرف یا فرمتی به غیر از آن هایی که بالا ذکر شد به تابع ارسال کنید، کاملا توسط تابع نادیده گرفته می شود. همچنین، اگر هر یک از حروف بالا به مقدار غیرمجاز اعمال شود، تابع مقدار NULL را برمی گرداند.


مثال:
آموزش SQL

به طور پیش فرض، هنگامی که تابع FORMAT() را فراخوانی می کنید، موتور DB به زبانی که در رایانه بکار می رود (زبان مورد استفاده در سیستم ویندوز) و نیز تنظیمات کنترل پنل اشاره می کند. اگر می خواهید از فرمت های زبان دیگری استفاده کنید، در آن صورت بایستی آرگومان سوم، culture، را نیز مشخص کرده و به تابع پاس دهید. این آرگومان که به عنوان یک رشته به تابع نام برده ارسال می شود، زبانی را که تابع بایستی از قوانین آن پیروی کند را مشخص می نماید.


مثال:
DECLARE @HourlySalary money,
        @Wage money,
        @UnitPrice decimal(8, 4),
        @FiscalBudget bigint;
SET @HourlySalary = 28.65;
SET @Wage = 28.65;
SET @UnitPrice = 349.95;
SET @FiscalBudget = 12640685;
SELECT FORMAT(@HourlySalary, N'C', N'en-CA') AS "Hourly Salary",
       FORMAT(@Wage, N'C', N'fr-ca') AS "Hourly Salary",
       FORMAT(@UnitPrice, N'c', N'en-gb') [Unit Price],
       FORMAT(@FiscalBudget, N'C', N'es-ES') AS N'Company Budget';
GO

آموزش SQL

تبدیل از integer به ASCII

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


 int ASCII(String)

این تابع به عنوان آرگومان یک رشته پذیرفته و در خروجی کد اسکی اولین کاراکتر رشته (از سمت چپ) را برمی گرداند.

مثال:
DECLARE @ES varchar(100)
SET @ES = N'El Salvador'
SELECT @ES AS ES
SELECT ASCII(@ES) AS [In ASCII Format]

نتیجه ی زیر را برمی گرداند:
آموزش SQL

تبدیل از ASCII به Integer

اگر کد اسکی یک کاراکتر را دارید و هم اکنون می خواهید خود کاراکتر را بدست بیاورید، در آن صورت می بایست تابع CHAR() را صدا بزنید. سینتکس آن به صورت زیر می باشد:

CHAR(int value) RETURNS char;

تابع مزبور به عنوان آرگومان یک مقدار عددی در قالب integer دریافت می کند. پس از تبدیل، تابع مورد نظر معادل اسکی آن عدد (آرگومان پاس داده شده به تابع) را برمی گرداند.


تبدیل به حروف کوچک

همان طور که می دانید، کاراکترهای رشته می توانند از حروف بزرگ، کوچک و علامت هایی تشکیل شده باشد (علامت ها کوچک و بزرگ ندارند). زمانی که یک رشته را به عنوان ورودی دریافت می کنید، برای تبدیل تمامی کاراکترهای تشکیل دهنده ی آن به حروف کوچک، می توانید از تابعLOWER() را صدا بزنید. نحوه ی کلی استفاده از آن:


 LOWER(String) RETURNS varchar;

این تابع به عنوان آرگومان یک رشته می گیرد. تمامی حروف بزرگ موجود در آن رشته (تشکیل دهنده ی رشته) با فراخوانی این تابع، به حروف کوچک تبدیل می شوند. آن دسته از حروفی که از قبل کوچک هستند و همچنین علامت ها، دست نخورده باقی می مانند. پس از انجام فرایند تبدیل، تابع LOWER() یک رشته ی جدید (که حروف آن به کوچک تبدیل شده) به عنوان خروجی برمی گدارند.


DECLARE @FIFA varchar(120)
SET @FIFA = N'Fédération Internationale de Football Association'
SELECT @FIFA AS FIFA
SELECT LOWER(@FIFA) AS Converted

نتیجه:
آموزش SQL

آموزش کاربردی: تبدیل کاراکترها و حروف بزرگ یک رشته به حروف کوچک

1. داخل پنجره ی Query Editor کلیک نموده، سپس کلیدهای Ctrl + A را همزمان فشار داده تا کلیه ی دستورات انتخاب شوند.
2. برای ایجاد یک تابع، دستورات زیر را وارد نمایید:


USE University4;
GO
-- ===================================================================
-- Function: CreateUsername
-- This function creates a user name based on the first and last names
-- ===================================================================
CREATE FUNCTION Academics.CreateUsername
	(@FirstName nvarchar(25), 
	 @LastName nvarchar(25))
RETURNS varchar(50)
AS
BEGIN
	DECLARE @Username AS nvarchar(50);
	SELECT @Username = LOWER(@FirstName) + LOWER(@LastName);
	RETURN @Username;
END
GO

3. با زدن کلید F5 دستورات را اجرا نمایید.
4. با استفاده از میانبر Ctrl + A، تمام دستورات جاری را انتخاب کنید.
5. تابع را تست کرده، سپس کد زیر را تایپ کنید:


SELECT University4.Academics.CreateUsername(N'Francine', N'Mukoko');
GO

6. F5 را زده تا دستور مورد نظر اجرا شود.


آموزش SQL

Sub-string: کاراکترهای آغازین یک رشته

Sub-string یک رشته است که از رشته ی دیگر استخراج شده است. برای تهیه ی sub-string، یک یا چند کاراکتر را مطابق نیاز از رشته ی مورد نظر جدا و در رشته ی دیگری قرار می دهیم.
Left sub-string یک یا چند کاراکتر است که از سمت چپ رشته ی مورد نظر استخراج می شود. برای ایجاد یک Left sub-string از رشته ی مدنظر، بایستی تابع LEFT() را فراخوانی کرد.


LEFT(String, NumberOfCharacters) RETURNS varchar

این تابع دو آرگومان می پذیرد. اولین آرگومان رشته ای است که کاراکترهایی از آن استخراج می شود. دومین آرگومان تعداد کاراکترهایی را که می بایست از سمت چپ رشته (آرگومان اول) بیرون کشیده شود را مشخص می کند. پس از اجرای عملیات، تابع LEFT() یک رشته ی جدید (زیر رشته) حاوی کاراکترهای استخراج شده از رشته ی مورد نظر برمی گرداند.


آموزش کاربردی: ایجاد یک زیر رشته با کاراکترهای سمت چپ رشته ی دیگر

1. کد موجود را برای استفاده از تابع LEFT() به صورت زیر ویرایش می کنیم:


Change the code in Query Editor to use the LEFT() function as follows:
USE University4;
GO
DROP FUNCTION Academics.CreateUsername;
GO
CREATE FUNCTION Academics.CreateUsername
	(@FirstName varchar(25), 
	 @LastName varchar(25))
RETURNS varchar(26)
AS
BEGIN
	DECLARE @Username AS varchar(26);
	SELECT @Username = LOWER(LEFT(@FirstName, 1) + 
			   LEFT(@LastName, 4));
	RETURN @Username;
END
GO

2. با فشردن کلید F5 دستورات را اجرا کنید.
3. در پنجره ی ویرایش کوئری (Query Editor) کلیک نمایید.
4. برای تست تابع مورد نظر، دستورات زیر را درج نمایید:


SELECT University4.Academics.CreateUsername(N'Francine', N'Mukoko');
GO

5. دستور را در پنجره اجرا کنید.


آموزش SQL

6. در فراخوانی تابع، تعداد کاراکترهای متغیر @lastname را به کمتر از 5 کاراکتر کاهش دهید، مانند "Um"، سپس دستور را اجرا کنید.


مثال:
آموزش SQL

7. در پنجره ی Query Editor کلیک کرده، سپس کلیدهای Ctrl + A را همزمان فشار دهید تا مجموعه دستورات انتخاب شوند.
8. برای بکاربردن تابع، کافی است دستور زیر را تایپ کنید:

USE University4;
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, Username, Gender)
VALUES(N'25506037', N'Matthew', N'Andy', N'Agurs', Academics.CreateUsername(N'Matthew', N'Agurs'), N'Male'),
      (N'88167413', N'Thomas',  N'Harold', N'Aldredge', Academics.CreateUsername(N'Thomas', N'Aldredge'), N'Male');
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, Username, Gender)
VALUES(N'63358761', N'Martin',  N'Aldredge', Academics.CreateUsername(N'Martin', N'Aldredge'), N'Male'),
      (N'92739470', N'Maggie', N'Altobello', Academics.CreateUsername(N'Maggie', N'Altobello'), N'Female');
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, Username, Gender)
VALUES(N'64702716', N'Frank', N'Alton', Academics.CreateUsername(N'Frank', N'Alton'), N'Male'),
      (N'25117160', N'Freida', N'Alvarez', Academics.CreateUsername(N'Freida', N'Alvarez'), N'Female');
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, Username)
VALUES(N'30493386', N'Hahia', N'Kwok', Academics.CreateUsername(N'Hahia', N'Kwok'));
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, Username, Gender)
VALUES(N'69631417', N'Simonne', N'Alvaro', Academics.CreateUsername(N'Simonne', N'Alvaro'), N'Female');
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, Username, Gender)
VALUES(N'76088478', N'Tricia', N'Catherine', N'Anderson', Academics.CreateUsername(N'Tricia', N'Anderson'), N'Female');
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, Username, Gender)
VALUES(N'77630549', N'Patrick', N'Amaleck', Academics.CreateUsername(N'Patrick', N'Amaleck'), N'Male'),
      (N'29502309', N'Maria', N'Anderson', Academics.CreateUsername(N'Maria', N'Anderson'), N'Female');
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, Username, Gender)
VALUES(N'68903849', N'Katherine', N'Jennie', N'Sandford', Academics.CreateUsername(N'Katherine', N'Sandford'), N'Female');
GO

9. F5 را فشار داده تا کد اجرا شود.


استخراج کاراکتر از سمت راست یک رشته

بجای کاراکترهای آغازین یک رشته (کاراکترهای سمت چپ)، شاید لازم باشد رشته ی جدیدی متشکل از کاراکترهای سمت راست رشته ی مورد نظر (کاراکترهای پایانی) ایجاد کنید. برای این منظور، T-SQL تابع RIGHT() را ارائه می دهد. نحوه ی بکارگیری آن به شرح زیر می باشد:


RIGHT(String, NumberOfCharacters) RETURNS varchar;

همان طور که در مثال فوق مشاهده می کنید، این تابع دو پارامتر ورودی می پذیرد. اولین آرگومان همان رشته ای است که کاراکترهای سمت راست آن استخراج می شوند. دومین آرگومان تعداد کاراکترهایی که باید از سمت راست رشته بیرون کشیده شود و زیر رشته را تشکیل می دهد، مشخص می کند.


آموزش کاربردی: استخراج کاراکترهای سمت راست رشته

1. با اعمال کلیدهای Ctrl + A تمامی دستورات موجود در پنجره ی ویرایش کوئری را انتخاب کنید.
2. برای تعریف تابع جدید، مجموعه دستورات زیر را وارد نمایید:


-- ==================================================
-- Function: Last4DigitsOfSSN
-- Description: This function finds the last 4 digits 
-- 		of a US Social Security Number
-- ==================================================
CREATE SCHEMA Administration;
GO
CREATE FUNCTION Administration.Last4DigitsOfSSN(@SSN nvarchar(12))
RETURNS nchar(4)
AS
BEGIN
	RETURN RIGHT(@SSN, 4);
END
GO

3. کلید F5 را فشار دهید تا دستور مورد نظر اجرا شود.
4. داخل Query Editor کلیک نموده، Ctrl + A را بزنید.
5. برای تست تابع، دستورات زیر وارد کنید:


SELECT University4.Administration.Last4DigitsOfSSN(N'836483846');
GO

6. دستور را اجرا نمایید.


آموزش SQL

جایگزینی در رشته

یکی از ملال آورترین سناریوهایی که ممکن است در کار با رشته، با آن مواجه شوید، مدیریت رشته ای است که دربردارنده ی کاراکترهای غیرمنتظره است. علتش می تواند از نحوه ی فرمت بندی رشته یا هر چیز دیگری نشات بگیرید. مثالی را در نظر بگیرید که در آن، شما درخواست یک شماره تلفن از کاربر را دارید. در پاسخ به درخواست، کاربر ممکن است رشته را با فرمت های مختلفی در اختیار شما قرار دهد. مثال: 000-000-0000، 0000000000، (000) 000-0000، 000.000.0000 . تمامی قالب های ذکر شده، فرمت های مجاز شماره تلفن در کانادا و آمریکا هستند، اما اگر همین رشته را در عملیات مورد استفاده قرار دهید، ممکن است نتیجه ی پیشبینی نشده دریافت کنید.
یک روش برای حل این مسئله، حذف کاراکترهای غیرمجاز یا ناخواسته از رشته ی مورد نظر می باشد. این عملیات می تواند جایگزینی برخی از کاراکترها با کاراکترهای دیگر باشد.
برای جایگزین کردن یک کاراکتر و یا زیر رشته ای از یک رشته، می توان تابعREPLACE() را صدا زد. دیگر این تابع، مقدار پارامتر FindString را در String جستجو می کند و تمام آنها را با مقدار ReplaceWith جایگزین می نماید.


REPLACE(String, FindString, ReplaceWith) RETURNS varchar;

یا
REPLACE(String, FindString, ReplaceWith) RETURNS binary;

همان طور که در بالا نیز گفته شد، این تابع سه آرگومان می گیرد. اولین آرگومان رشته ای است به عنوان مرجع یا reference مورد استفاده قرار می گیرد. دومین آرگومان، FindString کاراکتری یا زیر رشته ای است که در آرگومان String جستجو می شود. چنانچه دومین آرگومان در رشته ی مورد نظر یافت شد، در آن صورت مقدار آرگومان سوم (ReplaceWith) جایگزین آن در رشته می شود (در جای آن رشته یا کاراکتر می نشیند).


استفاده از تابع REPLACE()

1. در پنجره ی Query Editor کلیک کرده و تمامی دستورات داخل آن را با Ctrl + A یکجا انتخاب کنید.
2. برای استفاده از تابع REPLACE()، کد زیر را تایپ کنید:


DROP FUNCTION Administration.Last4DigitsOfSSN;
GO
CREATE FUNCTION Administration.GetLast4DigitsOfSSN(@SSN varchar(12))
RETURNS char(4)
AS
BEGIN
	DECLARE @StringWithoutSymbol As varchar(12);
	-- First remove empty spaces
	SET @StringWithoutSymbol = REPLACE(@SSN, N' ', N'');
	-- Now remove the dashes "-" if they exist
	SET @StringWithoutSymbol = REPLACE(@StringWithoutSymbol, N'-', N'');
	RETURN RIGHT(@StringWithoutSymbol, 4);
END
GO

3. با اعمال کلید F5، کد را اجرا کنید.
4. برای تست تابع، دستورات زیر را تایپ کنید:


SELECT University4.Administration.GetLast4DigitsOfSSN(N'244-04-8502');
GO

5. برای اجرای دستور مورد نظر، کلیدF5 را فشار دهید.


توابع ریاضی: عملگر مثبت و منفی

در ریاضیات، یک عدد که کوچکتر از 0 باشد منفی و اگر بزرگتر و مساوی 0 باشد مثبت محسوب می شود. عدد منفی بایستی همراه خود عملگر – را داشته باشد. اگر عدد مورد نظر مثبت است، داشتن یا نداشتن عملگر مثبت در سمت چپ آن کاملا اختیاری می باشد. عددی که هیچ عملگر مثبت و منفی را به همراه نداشته باشد، عدد مثبت یا بی علامت درنظر گرفته می شود. نمادی که در سمت چپ عدد قرار می گیرد و آن را مثبت یا منفی معرفی می کند، علامت یا sign آن عدد نامیده می شود. گاهی لازم است عددی که به برنامه ارائه می شود را پیش از اقدام به اجرای هرگونه عملیات، بررسی کرده و از مثبت یا منفی بودن آن مطلع شد.
برای بررسی و پی بردن به مثبت یا منفی بودن یک عدد، T-SQL تابعSIGN() را تعبیه کرده است.


 SIGN(Expression)

این تابع به عنوان ورودی یک عدد و یا اسم یک متغیر که دبردارنده ی مقدار عددی است می پذیرد. مفسر سپس عدد را بررسی می کند:
1. اگر پارامتر ورودی یا در فرمول Expression مقداری مثبت باشد، تابع 1 را برمی گرداند


مثال:
DECLARE @Number As int;
SET @Number = 24.75;
SELECT SIGN(@Number) AS [Sign of 1058];
GO

آموزش SQL

2. چنانچه آرگومان null باشد، تابع 0 را برمی گرداند:


DECLARE @Number As int;
SET @Number = 0;
SELECT SIGN(@Number) AS [Sign of Number];
GO

آموزش SQL

3. در صورتی که مقدار پارامتر ورودی، عددی منفی باشد، تابع 1- را به عنوان خروجی بازمی گرداند.


DECLARE @Number As int;
SET @Number = -57.05;
SELECT SIGN(@Number) AS [Sign of -57.05];
GO

آموزش SQL

با بهره گیری از این امکان (تابع SIGN())، می توان به منفی و مثبت یا NULL بودن یک مقدار پی برد. کافی است مقدار (یا متغیر) را به تابع مذکور پاس داده و با استفاده از یک عملگر منطقی، علامت مقدار را بررسی نمایید.


قدر مطلق یک عدد

سیستم عددی اعشاری (decimal)، تمامی اعدادی که از منفی بی نهایت آغاز شده و تا مثبت بی نهایت ادامه دارند را شامل می شود. اعداد بسته به موقعیتشان قبل و بعد از صفر، که خود خنثی تلقی می شود، یا مثبت هستند و یا منفی. در برخی عملیات، مقدار عددی بایستی حتما مثبت باشد، حتی اگر به صورت منفی در اختیار ما قرار گرفته باشد. قدر مطلق عدد x زمانی خود x است که مقدار ورودی مثبت باشد. در صورتی که مقدار ورودی منفی باشد، قدر مطلق عدد مورد نظر معادل مثبت همان عدد خواهد بود. برای مثال، قدر مطلق 12 خود 12 می باشد، در حالی که قدر مطلق -12 عدد 12 می باشد.
برای بدست آوردن قدر مطلق یک عدد، می توان تابعABS() را بکار برد. نحوه ی استفاده از آن در زیر شرح داده شده است:


     ABS(Expression) RETURNS Data Type of Argument

این تابع یک متغیر یا عدد به عنوان آرگومان می گیرد، قدر مطلق را محاسبه کرده و در خروجی نمایش می دهد.


DECLARE @NumberOfStudents INTEGER;
SET @NumberOfStudents = -32;
SELECT ABS(@NumberOfStudents) AS [Number of Students];
GO

نتیجه ی زیر حاصل می گردد:


آموزش SQL

بدست آوردن حد بالا یا سقف یک عدد

عدد اعشاری 12.155 را در نظر بگیرید. این عدد بین دو عدد صحیح 12 و 13 قرار دارد.


آموزش SQL

حال عدد -24.06 را درنظر بگیرید. عدد ذکر شده بین دو عدد -24 و -25 قرار می گیرد. از آنجایی که این عدد منفی می باشد، -24 از -25 بزرگتر محسوب می شود. در علم جبر، حد بالا یا سقف یک عدد بزرگترین عدد صحیح نزدیک به مقدار عددی مورد نظر می باشد. برای مثال حد بالای 12.155، عدد 13 است، زیرا این عدد بزرگترین عدد صحیح نزدیک به مقدار 12.155 محسوب می شود.
برای بدست آوردن حد بالای یک عدد، T-SQL تابعCEILING() را ارائه می دهد. نحوه ی کلی استفاده از آن به صورت زیر می باشد:


CEILING(Expression)

این تابع یک عدد یا متغیر که عدد در آن ذخیره شده را به عنوان پارامتر ورودی می پذیرد. پس از اجرا، در صورت موفقیت، تابع ذکر شده یک عدد با دو رقم اعشار برمی گرداند که بزرگتر یا مساوی مقدار پارامتر ورودی تابع (Expression) می باشد.


مثال:
DECLARE @Number1 As Numeric(6, 2),
		@Number2 As Numeric(6, 2)
SET @Number1 = 12.155;
SET @Number2 = -24.06;
SELECT CEILING(@Number1) AS [Ceiling of 12.155],
       CEILING(@Number2) AS [Ceiling of -24.06];
GO

خروجی:


آموزش SQL

در زیر همان نتیجه به نحوی دیگر نمایش داده شده است:


DECLARE @Number1 As Numeric(6, 2),
	@Number2 As Numeric(6, 2)
SET @Number1 = 12.155;
SET @Number2 = -24.06;
PRINT N'The ceiling of 12.155 is ' + 
      TRY_CONVERT(varchar(10), CEILING(@Number1));
PRINT N'The ceiling of -24.06 is ' +
      TRY_CONVERT(varchar(10), CEILING(@Number2));
GO

نتیجه:
آموزش SQL

محسابه ی حد پایین یک عدد

دو عدد اعشاری 128.44 و -36.72 را درنظر بگیرید. 128.44 بین دو عدد 128 و 129 قرار دارد. بدیهی است که مقدار کوچکتر عدد 128 می باشد. حال عدد −36.72 را درنظر بگیرید. این عدد منفی بوده و بین مقدار −37 و 36− قرار می گیرد. از آنجایی با عدد منفی سروکار داریم، عدد بزرگ 36− می باشد. کوچکترین مقدار عدد صحیح یک عدد، حد پایین یا کف آن عدد خوانده می شود. بر این اساس، حد پایین 128.44 ، مقدار 128 و حد پایین36.72 نیز عدد −37 می باشد.
برای دست آوردن حد پایین یک عدد، زبان T-SQL تابع FLOOR() حد پایین عدد را برمی گرداند. ساختار دستوری آن به شکل زیر می باشد:


 FLOOR(Expression)

این تابع به عنوان ورودی، یک مقدار عددی یا متغیر که عدد در آن ذخیره شده باشد، می گیرد. اگر تابع با موفقیت محسبات را انجام دهد، به عنوان خروجی نزدیکترین عدد صحیح کوچکتر از پارامتر ورودی را برمی گرداند. به عنوان مثال اگر 6.7 را به عنوان ورودی پاس دهید، در خروجی 6 را برمی گرداند. نمونه:


DECLARE @Number1 As Numeric(6, 2),
	@Number2 As Numeric(6, 2);
SET @Number1 = 128.44;
SET @Number2 = -36.72;
SELECT FLOOR(@Number1) AS [Floor of 128.44],
       FLOOR(@Number2) AS [Floor of -36.72];
GO

نتیجه:
آموزش SQL

محاسبه ی مقدار نمایی یک عدد (Exponent)

برای بدست آوردن مقدار نمایی یک عدد، T-SQL تابع EXP() را ارائه می دهد. نحوه ی کلی استفاده از آن به شرح زیر می باشد:


 EXP(Expression)

این تابع به عنوان آرگومان یک عدد یا متغیر حاوی مقدار عددی می پذیرد.


مثال:
DECLARE @Number As Numeric(6, 2);
SET @Number = 6.48;
SELECT EXP(@Number) AS [Exponent of 6.48];
GO

نتیجه ی زیر را برمی گرداند:


آموزش SQL

به توان بردن یک عدد (power)

بوسیله‌ی تابع POWER می‌توان اعداد را به توان رساند. این تابع آرگومان اول را به توان آرگومان دوم می برد. ساختار دستوری آن به صورت زیر می باشد:


POWER(x, y)

این تابع دو آرگومان الزامی می گیرد که اولین عدد پایه (x) و دومین عددی است که عدد اول به توان آن می رسد (y).


مثال:
DECLARE @x As Decimal(6, 2),
	@y As Decimal(6, 2);
SET @x = 20.38;
SET @y = 4.12;
SELECT POWER(@x, @y) AS [Power of 20.38 raised to 4.12];
GO

نتیجه: آموزش SQL

محاسبه ی لگاریتم طبیعی یک عدد

برای بدست آوردن لگاریتم طبیعی یک عدد، زبان Transact-SQL تابعLOG() را فراهم می کند. سینتکس:


LOG(Expression)

این تابع به عنوان آرگومان یک مقدار عددی یا اسم متغیر که دربردارنده ی مقدار عددی است می پذیرد. پس از انجام محاسبات، این تابع لگاریتم طبیعی مقدار آرگومان پاس داده شده به آن را برمی گرداند.


مثال:
DECLARE @Number As Decimal(6, 2);
SET @Number = 48.16;
SELECT LOG(@Number) AS [Natural Logarithm of 48.16];
GO
This would produce:

نتیجه ی زیر را بدست می دهد:


آموزش SQL

لگاریتم در مبنای 10 یک عدد

برای محاسبه ی لگاریتم در مبنای 10 یک عدد، T-SQL تابع LOG10() را ارائه می دهد. نحوه ی نگارش آن به شرح زیر می باشد:


LOG10(Expression)

عددی که مورد ارزیابی قرار می گیرد، به عنوان آرگومان X به تابع ارسال می شود. تابع ذکر شده لگاریتم در مبنای 10 یک عدد را بر اساس فرمول زیر برمی گرداند:


 y = log10x

که معادل این فرمول می باشد:


x = 10y

مثال:
DECLARE @Number As Decimal(6, 2);
SET @Number = 48.16;
SELECT LOG10(@Number) AS [Base-10 Logarithm of 48.16];
GO

نتیجه ی زیر را بدست می دهد:


آموزش SQL

ریشه ی دوم یا جذر یک عدد

برای بدست آوردن ریشه ی دوم یک عدد، T-SQL تابعSQRT() را ارائه می کند. نحوه ی استفاده از آن به شرح زیر می باشد:


SQRT(Expression)

تابع نام برده فقط یک آرگومان به صورت عدد مثبت decimal می گیرد. در صورتی که عدد مورد نظر مثبت باشد، پس از انجام محاسبات، تابع SQRT() ریشه ی دوم آرگومان پاس داده شده را برمی گرداند.


مثال:
DECLARE @Number As Decimal(6, 2);
SET @Number = 48.16;
SELECT SQRT(@Number) AS [The square root of 48.16 is];
GO

نتیجه ی زیر را تولید می کند:


آموزش SQL

در صورت پاس دادن عدد منفی به عنوان آرگومان، خطا دریافت خواهید کرد:


آموزش SQL

برای پی بردن به مثبت یا منفی بودن پارامتر ورودی، می توان از یک دستور کنترلی بهره گرفت.


توابع محاسباتی

آموزش SQL

دایره مکان هندسی نقاطی از صفحه است که فاصله‌شان از نقطه ی ثابتی واقع در آن صفحه، مقدار ثابتی باشد. نقطه ثابت مرکز دایره و مقدار ثابت اندازهٔ شعاع دایره (Radius) نامیده می‌شود. خطی که تمام نقاط با فاصله ی مساوی از مرکز را به هم متصل می کند، محیط دایره را خوانده می شود. قطر دایره فاصله ی بین دو نقطه از محیط تا مرکز آن نامیده می شود. به عبارتی دیگر، قطر دو برابر شعاع دایره محسوب می شود.
برای مدیریت سنجش، اندازه گیری و دیگر عملیات مربوطه، محیط دایره به 360 بخش تقسیم می شود. هر یک از این بخش ها، یک درجه نامیده می شود. واحد نمایش درجه، همان درجه بوده و با نماد ° نمایش داده می شود. یک دایره همان طور که قبلا ذکر شد، 360 درجه یا 360° می باشد. فاصله ی بین دو نقطه ی A و B در واقع 15 درجه بخش از کل دایره محسوب می شود، که به صورت 15° نمایش داده می شود.
فاصله ی بین دو نقطه ی هم فاصله A و B، یک شکل دایره مانند است که در اصطلاح هندسی arc یا کمان خوانده می شود. زاویه، نسبت فاصله بین دو نقطه A و B محیط دایره تقسیم بر شعاع آن می باشد (زاویه یا گوشه یکی از مفاهیم هندسه است و به ناحیه‌ای از صفحه گفته می‌شود که بین دو نیم‌خط که سر مشترک دارند محصور شده‌است). می توان آن را به صورت زیر نوشت:


آموزش SQL

تابع PI()

نویسه ی π که به صورت PI نیز نوشته می شود، یک عدد است که در محاسبات مختلف ریاضی بکار می رود. مقدار تقریبی آن 3.1415926535897932 می باشد. ماشین حساب درون ساخته ی Microsoft Windows آن را بدین صورت نشان می دهد: 3.1415926535897932384626433832795 . برای محسابه ی مقدار PI، زبان T-SQL تابع PI() را ارائه می دهد. نحوه ی نگارش آن به ترتیب زیر می باشد:


PI()

رادیان

آموزش SQL

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


RADIANS(Expression)

تابع مزبور یک مقدار بر حسب درجه گرفته، در خروجی مقدار پارامتر ورودی را بر حسب رادیان برمی گرداند.
یک سیکل اندازه گیری گردش بدور دایره می باشد، به این خاطر که گردش لزوما کامل نمی باشد، بسته به شرایط، اندازه گیری بر اساس زاویه ای که در طول گردش تحت پوشش قرار گرفت، انجام می گیرد. یک سیکل ممکن است تنها بخشی از یک دایره را تحت پوشش قرار دهد. در چنین وضعیتی می گوییم گردش کل دایره را تحت پوشش قرار نداده است. البته سیکل می تواند کل محیط یا 360° از دایره را پوشش دهد. یک سیکل درواقع برابر است با رادیان تقسیم بر 2 * Pi.


محاسبه ی اندازه ی یک زاویه بر حسب درجه

اگر اندازه ی زاویه را به رادیان دارید و می خواهید آن را به درجه محاسبه کنید، در آن صورت تابع DEGREES() در خدمت شما است. ساختار نگارشی آن به صورت زیر می باشد:


DEGREES(Expression)

این تابع به عنوان آرگومان یک مقدار بر حسب رادیان دریافت می کند و در خروجی معادل آن را به درجه برمی گرداند.


توابع مثلثاتی

آموزش SQL

AB را طول بین دو نقطه ی A و B درنظر بگیرید، در مثلثات به آن وتر می گویند. وتر بلندترین ضلع در مثلث قائم‌الزاویه است. AC را طول یا فاصله ی بین دو نقطه ی A و C درنظر بگیرید، این خط ضلع مجاور بر وتر محسوب می شود. کسینوس زاویه ی نقطه ی A، نسبت AC/AB می باشد. نسبت ضلع مجاور بر وتر، AC، به طول وتر،.AB به عبارتی دیگر، در مثلث قائم‌الزاویه نسبت ضلع مجاور هر زاویه حاده به وتر را کسینوس آن زاویه می‌نامند.


آموزش SQL

مقدار برگشتی، نسبت، یک عدد با دو رقم اعشار بین -1 و 1 خواهد بود.
برای بدست آوردن کسینوس یک زاویه بایستی تابع COS() را فراخوانی کرد. نحوه ی کلی استفاده از آن:


 COS(Expression)

زاویه ای که باید محسابه شود به عنوان آرگومان به این تابع ارسال می گردد. تابع ذکر شده کسینوس عدد را در خروجی برمی گرداند.


مثال:
DECLARE @Angle As Decimal(6, 3);
SET @Angle = 270;
SELECT COS(@Angle) AS [Cosine of 270];
GO

نتیجه ی زیر حاصل می گردد:


آموزش SQL

محسابه ی سینوس یک عدد

AB را طول بین دو نقطه ی A و B درنظر بگیرید، در مثلثات به آن وتر می گویند. CB فاصله یا طول دو نقطه ی C و B محسوب می شود که در اصل ضلع مقابل نقطه ی A می باشد. سینوس نسبت CB به AB می باشد. نسبت ضلع مقابل یا همان CB به وتر یا AB.
برای محسابه ی سینوس یک عدد، T-SQL تابع SIN() را ارائه می کند:


SIN(Expression)

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


مثال:
DECLARE @Angle As Decimal(6, 3);
SET @Angle = 270;
SELECT SIN(@Angle) AS [Sine of 270];
GO

خروجی:
آموزش SQL

محسابه ی تانژانت یک عدد

آموزش SQL

تانژانت، نسبت BC بر روی AC می باشد (BC/AC). برای بدست آوردن تانژانت یک عدد، T-SQL تابع TAN() را ارائه می دهد.


TAN(Expression)

مثال:
DECLARE @Angle As Decimal(6, 3);
SET @Angle = 270;
SELECT TAN(@Angle) AS [Tangent of 270];
GO

DECLARE @Angle As Decimal(6, 3); SET @Angle = 270; SELECT TAN(@Angle) AS [Tangent of 270]; GO
آموزش SQL
  • 7185
  •    1316
  • تاریخ ارسال :   1394/11/13

دانلود PDF دانشجویان گرامی اگر این مطلب برای شما مفید بود لطفا ما را در GooglePlus محبوب کنید
رمز عبور: tahlildadeh.com یا www.tahlildadeh.com
ارسال دیدگاه نظرات کاربران
شماره موبایل دیدگاه
عنوان پست الکترونیک

ارسال

آموزشگاه برنامه نویسی تحلیل داده
آموزشگاه برنامه نویسی تحلیل داده

تمامی حقوق این سایت متعلق به آموزشگاه تحلیل داده می باشد .