آموزش جامع SQL Server (جلسه ۳۱: توابع رشته‌ای – بخش ۳)
1398/04/24 14:12 , میلاد صاحب نظر

آموزش جامع SQL Server (جلسه ۳۱: توابع رشته‌ای – بخش ۳)

در جلسه قبل بخش ۲ مطالب مبحث توابع رشته‌ای یا string را به صورت کامل توضیح دادیم. و توابع SOUNDEX، RTRIM، RIGHT، REVERSE، REPLICATE، REPLACE، QUOTENAME، PATINDEX و SPACE را به طور کامل و مثال محور بررسی کردیم و توضیح دادیم.

۱- نصب و آشنایی با SQL Server

۱۰-عملگرهای SET وCTEوPIVOT

۱۹-indexها بخش۱

۲۸-توابع تاریخ یا date بخش۲

۲-نوشتن query

۱۱-تغییر داده‌ها

۲۰-indexها بخش۲

۲۹-توابع رشته‌ای بخش۱

۳-محدود کردن ردیف‌ها

۱۲-تعریف داده‌ها بخش۱

۲۱-stored procedureها بخش۱

۳۰- توابع رشته‌ای بخش۲

۴-فیلتر کردن داده‌ها بخش ۱

۱۳-تعریف داده‌ها بخش۲

۲۲- stored procedureها بخش۲

۳۱- توابع رشته‌ای بخش۳

۵-فیلتر کردن داده‌ها بخش۲

۱۴-data typeهای بخش۱

۲۳- stored procedureها بخش۳

۳۲-توابع سیستمی بخش ۱

۶-اتصال جداول بخش۱

۱۵-data typeها بخش۲

۲۴-توابع تعریف شده توسط کاربر

۳۳-توابع سیستمی بخش۲

۷-اتصال جداول بخش۲

۱۶-ویژگی‌ها

۲۵-triggerها

۳۴-window functionها بخش۱

۸-گروه‌بندی داده‌ها

۱۷-expressionها

۲۶-توابع تجمعی

۳۵-window functionها بخش ۲

۹-subquery

۱۸-viewها

۲۷-توابع تاریخ یا date بخش۱

 

 در این جلسه، بخش سوم و آخر این مبحث را پوشش خواهیم داد. با ما همراه باشید!

تابع

توضیح

بخش ۱

ASCII

مقدار کد ASCII یک کاراکتر را بازمی‌گرداند.

CHAR

یک مقدار ASCII را به یک کاراکتر تبدیل می‌کند.

CHATINDEX

درون یک رشته که از یک موقعیت خاص شروع می‌شود به دنبال یک زیر رشته می‌گردد و موقعیت زیر رشته را بازمی‌گرداند.

CONCAT

دو یا تعداد بیشتری رشته را به یک رشته تبدیل می‌کند.

CONCAT_WS

چندین رشته را با یک علامت مجزا کننده، به یک رشته واحد تبدیل می‌کند.

DIFFERENCE

مقادیر ()SOUNDEX از دو رشته را با هم مقایسه می‌کند.

FORMAT

یک مقدار فرمت شده با یک فرمت خاص و رفتار (اختیاری) را بازمی‌گرداند.

LEFT

تعداد مشخص شده‌ای کاراکتر را از یک رشته کاراکتر که از سمت چپ آغاز می‌شوند، استخراج می‌کند.

LEN

تعدادی کاراکتر را از یک رشته کاراکتر بازمی‌گرداند.

LOWER

کاراکترهای یک رشته را به حروف کوچک تبدیل می‌کند.

LTRIM

یک رشته جدید از یک رشته مشخص شده را پس از حذف جاهای خالی بازمی‌گرداند.

بخش ۲

NCHAR

کاراکتر Unicode با کد integer مشخص شده را بازمی‌گرداند، همان‌طور که توسط استاندارد UNICODE تعریف شده است.

PATINDEX

موقعیت آغازین اولین وقوع الگو در یک رشته را بازمی‌گرداند.

QUOTENAME

یک رشته UNICODE همراه با فاصله دهنده را بازمی‌گرداند تا رشته ورودی را تبدیل به یک شناساگر دارای فاصله معتبر تبدیل کند.

REPLACE

تمام وقوع‌های یک زیر رشته را درون یک رشته، با یک زیر رشته دیگر جایگزین می‌کند.

REPLICATE

یک رشته که به تعداد دفعات خاصی تکرار شده است را بازمی‌گرداند.

REVERSE

ترتیب برعکس یک رشته کاراکتر را بازمی‌گرداند.

RIGHT

تعداد خاصی از کاراکتر‌ها را از یک رشته کاراکتر که از سمت راست شروع می‌شود، بازمی‌گرداند.

RTRIM

یک رشته جدید از یک رشته مشخص شده را پس از حذف تمام جاهای خالی، بازمی‌گرداند.

SOUNDEX

یک کد (یا SOUNDEX) چهار کاراکتری از یک رشته را بر اساس اینکه چگونه گفته شده است، بازمی‌گرداند.

SPACE

یک رشته، از فضاها یا فاصله‌های تکرار شونده را بازمی‌گرداند.

بخش ۳

STR

داده‌های کاراکتری که از داده‌های عددی تبدیل شده است را بازمی‌گرداند.

STRING_AGG

ردیف‌های رشته‌های دارای یک مجزا کننده خاص را به یک رشته جدید متصل می‌کند.

STRING_ESCAPE

کاراکترهای خاصی از یک رشته را رها می‌کند و یک رشته جدید از کاراکترهای رها شده بازمی‌گرداند.

STRING_SPLIT

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

STUFF

بخشی از یک رشته را حذف می‌کند و سپس یک زیر رشته دیگر را با شروع از یک موقعیت خاص، به رشته اضافه می‌کند.

SUBSTRING

یک زیر رشته از درون یک رشته که از یک موقعیت خاص آغاز می‌شود و دارای طول خاصی است، استخراج می‌کند.

TRANSLATE

چندین تک کاراکتر را در یک عملیات جایگزین کرده و تک به تک ترجمه می‌کند.

TRIM

یک رشته جدید از یک رشته مشخص شده را پس از حذف تمام جاهای خالی ابتدا و انتها و بین حروف، بازمی‌گرداند.

UNICODE

مقدار integer از یک کاراکتر را بازمی‌گرداند (همان‌طور که توسط استاندارد Unicode تعریف شده است).

UPPER

یک رشته را به حروف بزرگ تبدیل می‌کند.

تابع STR در SQL Server

از تابع ()STR برای تبدیل مقدار عددی به مقدار کاراکتری استفاده می‌شود.

کد زیر عمل نمی‌کند:

SELECT 'index' + 1;

پیغام خطا به این صورت است:

Conversion failed when converting the varchar value 'index' to data type int.

برای درست کردن آن، باید عدد 1 را با استفاده از تابع ()STR به رشته ‘1’ تبدیل کنید:

SELECT 
    'index' + STR(1,1,1) result;

کد زیر، syntax تابع ()STR را نشان می‌دهد:

STR ( float_expression [ , length [ , decimal ] ] )  

در این syntax:

  • Float_expression یک عبارت عددی است که مقدار آن یک عدد تقریبی با یک نقطه اعشار است.

  • Length طول کلی رشته بازگشتی است. این کد شامل یک نقطه اعشار، علامت، ارقام و spaceها است. اگر صریحاً خودتان آن را مشخص نکنید، طول آن به صورت پیش‌فرض ۱۰ خواهد بود. اگر length صفر باشد، تابع مقدار NULL را بازمی‌گرداند.

  • Decimal تعداد محل‌های سمت راست نقطه اعشار از float_expression است که قرار است بازگردانده شود. Decimal باید کمتر یا برابر با 16 باشد. اگر بیشتر از 16 باشد، آنگاه تابع نتیجه را به 16 محل در سمت راست نقطه اعشاری اصلاح می‌کند.

مثال‌های تابع ()STR در SQL Server

این مثال از تابع ()STR برای تبدیل یک عدد که شامل 6 رقم و یک نقطه اعشاری می‌باشد، به یک رشته کاراکتر 6 حرفی با دو محل برای ارقام اعشار استفاده می‌کند:

SELECT 
    STR(123.456, 6, 2) result

خروجی به این شکل است:

result
------
123.46
 
(1 row affected)

اگر طول عدد از طول مشخص شده بیشتر شود، تابع ()STR همان‌طور که در مثال زیر مشاهده می‌کنید، مقدار ‘**’ را بازمی‌گرداند:

SELECT STR(987.65,2,2) result;

کد زیر، خروجی را نشان می‌دهد:

result
------
**
 
(1 row affected)

مثالی دیگری از طول ناکافی به این صورت است:

SELECT STR(987.65,1) result;

خروجی به این ترتیب است:

result
------
*
 
(1 row affected)

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

تابع STRING_AGG در SQL Server

تابع ()STRING_AGG یک تابع تجمعی است که ردیف‌های رشته‌ای را به هم متصل کرده و به یک رشته تبدیل می‌کند و هر کدام را با یک مجزا کننده خاص از هم جدا می‌کند. همچنین مجزا کننده را در پایان رشته نتیجه قرار نمی‌دهد.

کد زیر، syntax تابع ()STRING_AGG را نشان می‌دهد:

STRING_AGG ( input_string, separator ) [ order_clause ]

در این syntax:

  • Input_string در هنگام ادغام یا اتصال، می‌تواند هر نوعی که بتوان آن نوع را به VARCHAR و NVARCHAR تبدیل کرد، باشد.

  • Separator مجزا کننده برای رشته نتیجه است. مقدار آن می‌تواند یک علامت یا یک متغیر باشد.

  • Order_clause ترتیب نظم و مرتب‌سازی نتایج ادغام شده را با استفاده از دستور WITHIN GROUP مشخص می‌کند:
WITHIN GROUP ( ORDER BY expression [ ASC | DESC ] )

تابع ()STRING_AGG مقدار NULL را نادیده می‌گیرد و در هنگام انجام عمل ادغام، برای NULL مجزا کننده قرار نمی‌دهد.

مثال‌ تابع ()STRING_AGG در SQL Server

از جدول sales.customers از پایگاه داده نمونه Bikestores برای این مثال‌ استفاده می‌کنیم:

جدول sales.customers

استفاده از تابع ()STRING_AGG برای ایجاد مقادیر مجزا شده با ویرگول

این مثال از تابع ()STRING_AGG برای ایجاد لیست‌های ایمیل‌های مشتریان بر اساس شهر استفاده می‌کند:

SELECT
    city, 
    STRING_AGG(email,';') email_list
FROM
    sales.customers
GROUP BY
    city;

بخشی از خروجی به این شکل است:

نتایج

برای مرتب‌سازی لیست ایمیل، از دستور WITHIN GROUP استفاده می‌کنیم:

SELECT
    city, 
    STRING_AGG(email,';') 
        WITHIN GROUP (ORDER BY email) email_list
FROM
    sales.customers
GROUP BY
    city;

تصویر زیر، بخشی از خروجی را نشان می‌دهد:

نتایج

به این شکل، چگونگی استفاده از تابع ()STRING_AGG برای ادغام ردیف‌های رشته‌ای با هم و تبدیل آن‌ها به یک رشته و قرار دادن یک مجزا کننده مشخص شده بین آن‌ها را نیز آموختید.

تابع STRING_ESCAPE در  SQL Server

تابع ()STRING_ESCAPE کاراکترهای خاصی در یک رشته را رها می‌کند و رشته جدیدی با کاراکترهای رها شده بازمی‌گرداند. در حال حاضر، تابع ()STRING_ESCAPE فقط از کاراکترهای خاص JSON پشتیبانی می‌کند.

کد زیر، syntax تابع ()STRING_ESCAPE را نشان می‌دهد:

STRING_ESCAPE(input_string, type)

تابع ()STRING_ESCAPE دو آرگومان می‌پذیرد:

  • Input_string یک عبارت است که شامل رشته‌ای است که باید رها شود.

  • Type قوانین رهاسازی که اعمال خواهند شد را مشخص می‌کند. در حال حاضر، type فقط مقدار ‘json’ را می‌پذیرد.

جداول زیر، کاراکترهای خاص JSON قابل قبول برای رها شدن را نشان می‌دهند:

کاراکتر خاص
کد رمزگذاری شده

علامت دبل کوتیشن (“)

“/

بک اسلش (\)

|\

اسلش (/)

/\

Backspace

b\

Form feed

f\

خط جدید

n\

کاراکتر ابتدای خط (carriage return)

r\

تب افقی

t\

کاراکتر کنترل

کد رمزگذاری شده

CHAR(0)

u0000\

CHAR(1)

u0001\

CHAR(31)

u001f\

مثال تابع ()STRING_ESCAPE در SQL Server

این مثال از تابع ()STRING_ESCAPE برای رها سازی کاراکترهای خاص با پیروی از قوانین JSON استفاده می‌کند و یک رشته جدید متشکل از کاراکترهای رها شده بازمی‌گرداند.

SELECT 
    STRING_ESCAPE('[''	This is a special / "message" /'']', 'json') AS
    escapedJson;

خروجی به این شکل است:

escapedJson
--------------------------------------------
['\tThis is a special \/ \"message\" \/']
 
(1 row affected)

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

تابع STRING_SPLIT در SQL Server

تابع ()STRING_SPLIT یک مقدار جدولی است که یک رشته را به جدولی متشکل از ردیف‌هایی از زیر رشته‌ها بر اساس یک مجزا کننده خاص تبدیل می‌کند.

کد زیر، syntax تابع ()STRING_SPLIT را نشان می‌دهد:

STRING_SPLIT ( input_string , separator )  

در این syntax:

  • Input_string یک عبارت مبتنی بر کاراکتر است که مقدار آن یک رشته از نوع NCHAR، VARCHAR، NVARCHAR یا CHAR است.

  • Separator یک کاراکتر مورد استفاده به عنوان یک مجزا کننده برای جدا سازی است.

تابع ()STRING_SPLIT یک جدول تک ستونه بازمی‌گرداند، که نام ستون آن value است. این جدول به دست آمده شامل ردیف‌هایی است که دارای زیر رشته‌ها هستند.

توجه کنید که مقادیر موجود در ستون value می‌توانند به هر ترتیبی مرتب‌سازی شوند. برای به دست آوردن ترتیب مورد انتظارتان، باید دستور ORDER BY را به عبارت SELECT اضافه کنید:

ORDER BY value [ASC|DESC]

مثال‌های تابع ()STRING_SPLIT در SQL Server

اجازه دهید چند  مثال برای استفاده از تابع ()STRING_SPLIT حل کنیم.

الف) استفاده از تابع ()STRING_SPLIT برای تجزیه مقدار رشته‌ای جدا شده با ویرگول

این مثال از تابع ()STRING_SPLIT برای تجزیه یک لیست مقادیر مجزا شده از هم با ویرگول استفاده می‌کند:

SELECT 
    value  
FROM 
    STRING_SPLIT('red,green,,blue', ',');

خروجی به این ترتیب است:

value
---------------
red
green
 
blue
 
(4 rows affected)

سومین ردیف خالی است، چون رشته ورودی شامل دو ویرگول متوالی (,,) است. برای به دست آوردن زیر رشته‌های غیر خالی، می‌توانید به عبارت SELECT یک دستور WHERE اضافه کنید که در query زیر مشاهده می‌کنید:

SELECT 
    value  
FROM 
    STRING_SPLIT('red,green,,blue', ',')
WHERE
    TRIM(value) <> '';

خروجی به این ترتیب است:

value
---------------
red
green
blue
 
(3 rows affected)
ب) استفاده از تابع ()STRING_SPLIT برای تجزیه یک رشته مجزا شده با ویرگول در یک ستون

گاهی، جداول پایگاه داده نرمال‌سازی نشده‌اند. یک مثال معمول از این حالت، زمانی است که یک ستون می‌تواند چندین مقدار مجزا شده با ویرگول را ذخیره کند.

تابع ()STRING_SPLIT با تجزیه این ستون‌های چند مقداره، به شما کمک می‌کند جداولتان را نرمال‌سازی کنید.

اجازه دهید یک جدول نمونه برای مثالمان ایجاد کنیم.

ابتدا، یک جدول جدید به نام Sales.contacts ایجاد می‌کنیم که اطلاعات مخاطب را ذخیره می‌کند:

CREATE TABLE sales.contacts (
    id INT PRIMARY KEY IDENTITY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phones VARCHAR(500)
);

دوم، چند مخاطب به جدول اضافه می‌کنیم:

INSERT INTO 
    sales.contacts(first_name, last_name, phones)
VALUES
    ('John','Doe','(408)-123-3456,(408)-123-3457'),
    ('Jane','Doe','(408)-987-4321,(408)-987-4322,(408)-987-4323');

سوم، از تابع ()STRING_SPLIT برای تجزیه شماره‌های تلفن و از دستور CROSS APPLY برای برقراری ارتباط با جدول sales.contacts استفاده می‌کنیم:

SELECT 
    first_name, 
    last_name,
    value phone
FROM 
    sales.contacts
    CROSS APPLY STRING_SPLIT(phones, ',');

خروجی به این شکل است:

نتایج

پ) استفاده از تابع ()STRING_SPLIT برای یک تابع تجمعی

مثال زیر، مخاطبان و شماره‌های تلفن برای هر مخاطب را بازمی‌گرداند:

SELECT 
    CONCAT_WS(' ',first_name,last_name) full_name,
    COUNT(value) number_of_phones
FROM 
    sales.contacts
    CROSS APPLY STRING_SPLIT(phones, ',')
GROUP BY 
    CONCAT_WS(' ',first_name,last_name);

خروجی به این ترتیب است:

نتایج

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

تابع STUFF در SQL Server

تابع ()STUFF بخشی از یک رشته را حذف می‌کند و سپس یک زیر رشته به رشته اضافه می‌کند که آغاز آن در یک موقعیت مشخص شده قرار می‌گیرد.

کد زیر، syntax تابع ()STUFF را نشان می‌دهد:

STUFF ( input_string , start_position , length , replace_with_substring )

تابع ()STUFF چهار آرگومان می‌پذیرد:

  • Input_string رشته کاراکتری است که باید پردازش شود.

  • Start_position یک integer است که موقعیت شروع حذف و اضافه کردن را مشخص می‌کند. اگر start_position منفی، صفر یا بیشتر از طول رشته باشد، تابع مقدار NULL را بازمی‌گرداند.

  • Length تعداد کاراکترهایی که باید حذف شوند را مشخص می‌کند. اگر length منفی باشد، تابع مقدار NULL را بازمی‌گرداند. اگر length بیشتر از طول input_string باشد، تابع کل رشته را حذف می‌کند. در صورتی که length صفر باشد، آنگاه تابع مقدار replace_with_sustring را در ابتدای input_string قرار می‌دهد.

  • Replace_with_substring یک زیر رشته است که کاراکترهای length از input_string را در ابتدای start_position جایگزین می‌کند.

مثال‌های تابع ()STUFF در SQL Server

اجازه دهید چند مثال برای استفاده از تابع ()STUFF حل کنیم.

الف) استفاده از تابع ()STUFF برای اضافه کردن یک رشته به یک رشته دیگر در یک موقعیت خاص

این مثال، از تابع ()STUFF برای حذف سه کاراکتر اول رشته ‘SQL Tutorial’ و سپس اضافه کردن رشته ‘SQL Server’ به ابتدای رشته استفاده می‌کند:

SELECT 
    STUFF('SQL Tutorial', 1 , 3, 'SQL Server') result;

خروجی به این شکل است:

result
-------------------
SQL Server Tutorial
 
(1 row affected)
ب) استفاده از تابع ()STUFF برای تبدیل زمان از HHMM به HH:MM

مثال زیر، از تابع ()STUFF برای اضافه کردن دو نقطه (:) به وسط زمان با فرمت HHMM استفاده می‌کند و یک مقدار زمان جدید با فرمت HH:MM بازمی‌گرداند:

SELECT 
    STUFF('1230', 3, 0, ':') AS formatted_time;

خروجی به این شکل است:

formatted_time
--------------
12:30
 
(1 row affected)
پ) استفاده از تابع ()STUFF برای فرمت تاریخ از MMDDYYYY به MM/DD/YYYY

مثال زیر، تابع ()STUFF را دو بار فراخوانی می‌کند تا یک تاریخ را از MMDDYYYY به MM/DD/YYYY فرمت کند:

SELECT 
    STUFF(STUFF('03102019', 3, 0, '/'), 6, 0, '/') formatted_date;

خروجی به این ترتیب است:

formatted_date
--------------
03/10/2019
 
(1 row affected)
ت) استفاده از تابع ()STUFF برای پوشاندن شماره‌های کارت اعتباری

این مثال از تابع ()STUFF برای پوشاندن یک شماره کارت اعتباری استفاده می‌کند. فقط چهار کاراکتر آخر شماره کارت اعتباری نمایش داده می‌شوند:

DECLARE 
    @ccn VARCHAR(20) = '4882584254460197';
 
SELECT 
    STUFF(@ccn, 1, LEN(@ccn) - 4, REPLICATE('X', LEN(@ccn) - 4))
    credit_card_no;

خروجی به این صورت است:

credit_card_no
-----------------
XXXXXXXXXXXX0197
 
(1 row affected)

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

تابع SUBSTRING در SQL Server

تابع ()SUBSTRING یک زیر رشته استخراج می‌کند که دارای طول مشخص شده می‌باشد و از موقعیتی در رشته ورودی آغاز می‌شود.

کد زیر، syntax تابع ()SUBSTRING را نشان می‌دهد:

SUBSTRING(input_string, start, length);

در این syntax:

  • Input_string می‌تواند یک عبارت ntext، text، binary، character یا image باشد.

  • Start یک integer است که موقعیتی را بازمی‌گرداند که زیر رشته بازگشتی از آنجا شروع می‌شود. توجه کنید که اولین کاراکتر در input_string مقدار 1 است، نه صفر.

  • Length یک integer مثبت است که تعداد کاراکترهای زیر رشته‌ای که باید بازگردانده شود را مشخص می‌کند. اگر length منفی باشد، تابع ()SUBSTRING ارور می‌دهد. اگر start + length بزرگ‌تر از input_string باشد، زیر رشته از start شروع می‌شود و حاوی کاراکترهای باقیمانده از input_string است.

مثال‌های تابع ()SUBSTRING در SQL Server

بیایید چند مثال برای استفاده از تابع ()SUBSTRING حل کنیم تا بهتر عملکرد آن را درک کنید.

الف) استفاده از تابع ()SUBSTRING برای رشته‌ها

این مثال، یک زیر رشته با طول 6 استخراج می‌کند که از پنجمین کاراکتر در رشته ‘SQL Server SUBSTRING’ شروع می‌شود.

SELECT 
    SUBSTRING('SQL Server SUBSTRING', 5, 6) result;

خروجی به این شکل است:

result
------
Server
 
(1 row affected)
ب) استفاده از تابع ()SUBSTRING برای ستون‌های جدول

از جدول sales.customers در پایگاه داده نمونه Bikestores برای مثال استفاده می‌کنیم.

جدول sales.customers

این مثال، از تابع ()SUBSTRING برای استخراج دامنه از آدرس‌های ایمیل مشتریان استفاده می‌کند:

SELECT 
    email, 
    SUBSTRING(
        email, 
        CHARINDEX('@', email)+1, 
        LEN(email)-CHARINDEX('@', email)
    ) domain
FROM 
    sales.customers
ORDER BY 
    email;

تصویر زیر، بخشی از خروجی را نشان می‌دهد:

نتایج

نحوه عملکرد کد:

ابتدا، از تابع ()CHARINDEX برای جستجوی کاراکتر @ در ایمیل استفاده می‌کند. دامنه از کاراکتر @ به علاوه 1 شروع می‌شود.

سپس، از نتیجه تابع ()CHARINDEX استفاده می‌کنیم تا موارد زیر مشخص شوند:

  • موقعیت آغازین دامنه: CHARINDEX(‘@’, email) + 1

  • طول دامنه: (LEN(email) – CHARINDEX(‘@’, emali

برای شمارش تعداد ایمیل‌ها به ازای هر دامنه، می‌توانید از query زیر استفاده کنید:

SELECT 
    SUBSTRING(
        email, 
        CHARINDEX('@', email)+1, 
        LEN(email)-CHARINDEX('@', email)
    ) domain,
    COUNT(email) domain_count
FROM 
    sales.customers
GROUP BY
    SUBSTRING(
            email, 
            CHARINDEX('@', email)+1, 
            LEN(email)-CHARINDEX('@', email)
        );

خروجی به این صورت است:

نتایج

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

تابع TRANSLATE در SQL Server

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

کد زیر، syntax تابع ()TRANSLATE را نشان می‌دهد:

TRANSLATE(input_string, from_characters, to_characters);

در این syntax:

  • Input_string یک رشته است که باید جستجو شود. مقدار آن می‌تواند یک رشته، عبارت کاراکتری یا ستون باشد.

  • From_characters یک عبارت رشته‌ای است که حاوی کاراکترهایی است که باید جایگزین شوند.

  • To_characters یک عبارت رشته‌ای است که حاوی کاراکترهای جایگزین شونده است.

اگر طول‌های from_characters و to_characters متفاوت باشند، آنگاه تابع ()TRANSLATE ارور می‌دهد.

اگر هر کدام از آرگومان‌ها NULL باشند، تابع ()TRANSLATE مقدار NULL را بازمی‌گرداند.

مثال‌های تابع ()TRANSLATE در SQL Server

مثال زیر از تابع ()TRANSLATE برای جایگزینی کروشه‌ها با پرانتزها استفاده می‌کند:

SELECT 
    TRANSLATE('[408] 555 6789','[]','()') result;

خروجی به این شکل است:

result
--------------
(408) 555 6789

کد بالا دقیقاً مثل این است که دو بار تابع ()REPLACE را فراخوانی کنیم:

SELECT 
    REPLACE(REPLACE('[408] 555 6789','[','('),']',')') result;

()TRANSLATE در برابر ()REPLACE

رفتار تابع ()TRANSLATE مشابه چندین بار فراخوانی تابع ()REPLACE است.

به هر حال، تابع ()TRANSLATE  تمام وقوع‌های یک کاراکتر را با یک کاراکتر جدید جایگزین نمی‌کند. این تفاوت بین تابع ()TRANSLATE و فراخوانی چندین دفعه‌ای تابع‌های ()REPLACE است، هر فراخوانی تابع ()REPLACE، تمام کاراکترهای مربوطه را جایگزین می‌کند.

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

تابع TRIM در SQL Server

تابع ()TRIM فضا یا spaceها یا کاراکترهای مشخص شده از هر دو طرف رشته را حذف می‌کند. کد زیر، syntax تابع ()TRIM را نشان می‌دهد:

TRIM([removed_characters FROM] input_string)

در این syntax:

  • Removed_characters یک رشته، متغیر یا ستون جدول دارای نوع کاراکتری غیر LOB( ینی NCHAR، VARCHAR، NVARCHAR یا CHAR) است که حاوی کاراکترهایی است که حذف خواهند شد. توجه کنید که نوع‌های (NVARCHAR(MAX و (VARCHAR(MAX مجاز نیستند. آرگومان remove_characters اختیاری است. اگر آن را مشخص نکنید،، تابع ()TRIM بعد از حذف تمام فضاها یا Spaceهای ابتدا و انتها از input_string، یک رشته بازمی‌گرداند. به این شکل، تأثیر مشابه با اثر استفاده از هر تو تابع‌های ()LTRIM و ()RTRIM دارد: ((RTRIM(LTRIM(input_string.

  • Input_string یک عبارت با هر نوع کاراکتری (NCHAR، VARCHAR، NVARCHAR یا CHAR) است که در آن removed_characters باید حذف شود.

تابع ()TRIM یک رشته بازمی‌گرداند که در آن removed_characters از هر دو طرف رشته حذف شده‌اند. اگر input_string برابر با NULL باشد، تابع مقدار NULL را بازمی‌گرداند.

مثال‌های تابع ()TRIM در SQL Server

اجازه دهید چند مثال برای استفاده از تابع ()TRIM حل کنیم.

الف) حذف spaceهای دو طرف از یک رشته

مثال زیر از تابع ()TRIM برای حذف تمام فضاها یا Spaceهای دو طرف از رشته ‘  Test string  ‘ را حذف می‌کند:

SELECT 
    TRIM('  Test string    ');

خروجی به این ترتیب است:

result
-----------------
Test string
 
(1 row affected)
ب) استفاده از تابع ()TRIM برای حذف کاراکترهای مشخص شده از دو طرف یک رشته

این مثال، از تابع ()TRIM برای حذف کاراکترهای نقطه (.) و $ از رشته ‘..Hello$$$’ استفاده می‌کند:

SELECT 
    TRIM('.$' FROM '$$$Hello..') result

خروجی به این شکل است:

result
----------
Hello
 
(1 row affected)
پ) استفاده از تابع ()TRIM برای حذف spaceهای دو طرف در یک ستون از جدول

این کد، از تابع ()TRIM برای حذف تمام Spaceهای دو طرف از مقادیر موجود در ستون street از جدول sales.customers استفاده می‌کند:

UPDATE 
    sales.customers
SET  
    street = TRIM(street);

به این صورت،، چگونگی استفاده از تابع ()TRIM برای حذف spaceها یا کاراکترهای مشخص شده از دو طرف یک رشته را آموختید.

تابع UPPER در SQL Server

تابع ()UPPER یک رشته ورودی را به حروف بزرگ تبدیل می‌کند. کد زیر، syntax تابع ()UPPER را نشان می‌دهد:

UPPER(input_string)

در این syntax، مقدار input_string می‌تواند یک رشته، متغیر، عبارت رشته‌ای کاراکتری یا ستون جدول باشد.

نوع input_string باید یک به صورت ضمنی قابل تبدیل شدن به VARCHAR باشد. در غیر این صورت،، باید از تاب ()CAST برای تبدیل غیر ضمنی یا صریح input_string استفاده کنید.

تابع ()UPPER  حروف بزرگ مقدار input_string را بازمی‌گرداند.

مثال‌های تابع ()UPPER در SQL Server

بیایید چند مثال برای استفاده از تابع ()UPPER حل کنیم.

الف) استفاده از تابع ()UPPER برای رشته‌ها

این مثال از تابع ()UPPER برای تبدیل رشته ‘sql’ به ‘SQL’ استفاده می‌کند:

SELECT 
    UPPER('sql') result;

 خروجی به این صورت است:

result
------
SQL
 
(1 row affected)
ب) استفاده از تابع ()UPPER برای ستون جدول

از جدول production.products از پایگاه داده نمونه استفاده می‌کنیم:

جدول production.products

کد زیر از تابع ()UPPER برای تبدیل نام محصولات به حروف بزرگ استفاده می‌کند:

SELECT 
    product_name, 
    UPPER(product_name) product_name_upper
FROM 
    production.products
ORDER BY 
    product_name;

تصویر زیر، بخشی از خروجی را نشان می‌دهد:

نتایج

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

خلاصه

بالاخره مبحث مهم و بزرگ توابع رشته‌ای یا string نیز به پایان رسید. در طول این سه جلسه در سه بخش تمام توابع این مبحث را به صورت مثال محور بررسی کردیم و به صورت کامل توضیح دادیم.

در این بخش توابع TRIM، TRANSLATE، SUBSTRING، STUFF، STRING_SPLIT، STRING_ESCAPE، STRING_AGG، STR و UPPER توضیح داده شده و بررسی شدند.

در جلسه آینده وارد مبحث توابع سیستمی در SQL Server خواهیم شد.

با MUG همراه باشید!

منبع: sqlservertutorial

 مطالب مرتبط

۱۳ دستور مهم SQL که هر برنامه‌نویس باید بداند
چگونه از حملات نفوذ کننده به SQL جلوگیری کنیم؟
 طبقه بندی زبان های برنامه نویسی
روش‌های هوشمندانه برای استفاده از "تلفیق" در SQL
 LINQ(زبان جستجوی یکپارچه)
برنامه نویسی شیءگرا چیست؟

از آخرین دوره های آموزشی و تخفیف ها مطلع شوید

با تکمیل فرم زیر ، از اخبار و اطلاعات به روز برنامه نویسی و تکنولوژی عقب نمانید

آخرین مطالب

آموزش جامع SQL Server (جلسه ۱۲)
آموزش جامع SQL Server (جلسه ۱۲)

دستور UPDATE در SQL Server برای تغییر داده‌های موجود در یک جدول، از دستور UPDATE به شکل زیر استفاده ...

آموزش جامع SQL Server (جلسه ۱۵)
آموزش جامع SQL Server (جلسه ۱۵)

دستور DROP TABLE در SQL Server گاهی، لازم است یک جدول که دیگر استفاده‌ای ندارد را حذف کنید. برای ...

آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)
آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)

بخش اول از آخرین مبحث دوره جامع آموزش SQL Server در جلسه قبلی بررسی شد. این مبحث که ...

آخرین دیدگاه ها

دیدگاه خود را درباره این پست بنویسید

فرم ارسال نظرات