تابع SQL Substring در ۵ مثال

پنج مثال از تابع SUBSTRING()

برای آنکه درک بهتری از نحوه عملکرد و استفاده از تابع SQL Substring به دست آورید، در این بخش قصد داریم به معرفی چند مثال مفید بپردازیم.

مثال اول؛ استخراج یک زیررشته از یک رشته تحت‌اللفظی

فرض کنید قصد دارید یک زیررشته را از رشته‌ای که متن آن برای شما کاملاً مشخص است، استخراج کنید. برای نمونه، این متن (This is the first substring example) را دارید و می‌خواهید زیررشته‌ای با طول ۱۰ را که از نهمین کاراکتر متن آغاز می‌شود، استخراج کنید. در این صورت، کافی است بدون هیچ گونه دردسری، کد زیر را با استفاده از تابع SQL Substring بنویسید:

SELECT SUBSTRING('This is the first substring example', 9, 10);

در این صورت، خروجی کد شما این زیررشته خواهد بود: ‘the first’. این ساده‌ترین شکلی است که می‌توانید از این تابع بهره بگیرید. به عبارتی دیگر، در مواردی که متن رشته شما روشن است، می‌توانید آن را به عنوان مقدار اولین آرگومان تابع وارد کنید. مقادیر دو آرگومان دیگر در تابع SQL Substring نیز که کاملاً واضح‌اند و به توضیح بیشتری نیاز ندارند.

جدول کارمندان

برای آنکه بتوانیم وارد مثال‌های بعدی از تابع SQL Substring بشویم، نیاز به یک جدول فرضی با عنوان employees داریم. از این جدول که آن را در تصویر زیر می‌بینید، در مثال‌های بعدی استفاده خواهیم کرد. ستون‌ها و مقادیر هر یک از آن‌ها در این تصویر کاملاً روشن‌ هستند. (این تصویر تنها شامل چند ردیف اول از جدول فرضی ماست)

مثال دوم؛ استخراج یک زیررشته از یک ستون

در مثال قبلی تابع SQL Substring، با روشی آشنا شدیم که در صورت داشتن یک رشته متن مشخص، می‌توان از آن استفاده کرد. حالا می‌خواهیم ببینیم در صورتی که رشته مشخصی برای استخراج زیررشته نداشته باشیم، چه می‌توان کرد؟!

در این مثال، آنچه برای ما مشخص است، ستونی است که زیررشته مورد نظر را می‌توان درون آن پیدا کرد. در این حالت، برای استفاده از تابع SQL Substring در جدول employees، می‌توان قطعه کدی مشابه کد زیر را نوشت:

SELECT first_name,
last_name,
email,
SUBSTRING(email, 1, 2) AS employee_initials
FROM employees;

جدولی که توسط این کد برگردانده می‌شود، در تصویر زیر مشخص است. این کد برای تمامی ردیف‌های جدول، به دو حرف اول از ستون email مراجعه می‌کند و آن را در ستونی با نام employee_initials نمایش می‌دهد.

مثال سوم؛ استخراج زیررشته بدون آرگومان length

آرگومان length تعیین‌گر طول زیررشته‌ای است که قصد استخراجش را داریم. حال آیا می‌توان بدون این آرگومان، از تابع SQL Substring استفاده کرد؟ باید گفت پاسخ مثبت است!

مثلاً فرض کنید قصد دارید سال استخدام یکی از کارمندان را از جدول استخراج کنید. اگر به جدولی که ما به عنوان نمونه آوردیم دقت کنید، می‌بینید که در آن جدول، تاریخ استخدام به همراه ماه و سال آورده شده است. حال آنکه ما تنها به سال استخدام نیاز داریم. برای رفع این مشکل، کدی مشابه آنچه در زیر می‌بینید، راهگشا خواهد بود:

SELECT first_name,
last_name,
start_date,
SUBSTRING(start_date, 4) AS start_year
FROM employees;

این کد برای هر ردیف به ستون start_date مراجعه می‌کند. چهارمین کاراکتر یافت‌شده در آن ستون را به عنوان آغاز زیررشته در نظر می‌گیرد (عدد ۴ در کد بالا مقدار آرگومان start است). سپس از آنجایی که آرگومان length مقداری ندارد، تا آخر رشته را بدون توجه به آنکه طول آن چه قدر باشد، می‌خواند و به عنوان خروجی تابع SQL Substring برمی‌گرداند. خروجی‌های این تابع را در ستون start_year در جدول زیر می‌بینید.

مثال چهارم، استفاده از POSITION()

به ستون email در جدول کارمندان نگاه کنید. بسیاری از شرکت‌ها عبارت پیش از @ در ایمیل کاربران خود را به عنوان نام کاربری آن‌ها استفاده می‌کنند. حال فرض کنید بخواهیم با استفاده از تابع SQL Substring این نام کاربری را استخراج کنیم؛ چه باید کرد؟

کافی است از کلمه کلیدی POSITION() استفاده کنیم که معادل آن در SQL Server و MySQL با نام CHARINDEX() شناخته می‌شود. اما چطور می‌توان از POSITION() در تابع SQL Substring استفاده کرد؟

به کد زیر نگاه کنید تا نحوه استفاده از آن را بیاموزید:

SELECT first_name,
last_name,
SUBSTRING (email, 1, POSITION('@' IN email)-1) AS username
FROM employees;

این کد، کاراکتر @ را در هر یک از ردیف‌های ستون email پیدا می‌کند. سپس با توجه به عمل -۱ به یک کاراکتر قبل از آن رجوع می‌کند. این کاراکتر را به عنوان آخرین کاراکتر زیررشته در نظر می‌گیرد. حال با توجه به اینکه مقدار start عدد ۱ است، از اولین کاراکتر تا کاراکتر پیش از @ را به عنوان خروجی خود برمی‌گرداند (تابع POSITION در واقع موقعیت کاراکتر ورودی خود را برمی‌گرداند). همان طور که در تصویر زیر می‌بینید، خروجی‌ها در ستونی با نام username نمایش داده می‌شوند.

مثال پنجم، استفاده از LENGTH() + POSITION()

اکنون به آخرین و پیچیده‌ترین مثال از تابع SQL Substring می‌رسیم. فرض کنید می‌خواهیم از ستون job_title (عنوان شغل)، موقعیت شغلی را استخراج کنیم. در زبان انگلیسی معمولاً موقعیت شغلی به عنوان آخرین کلمه در عنوان شغل نوشته می‌شود. مثلاً در جدول کارمندان، Junior Sales Assistant (دستیار فروش تازه‌کار) یکی از عنوان‌های شغل است. در این رشته، Assistant (دستیار) همان موقعیت شغلی هر کارمند است که اکنون قصد استخراج آن را داریم.

SELECT first_name,
last_name,
job_title,
SUBSTRING(job_title, LENGTH(job_title) - POSITION(' ' IN REVERSE(job_title))+2) AS position
FROM employees;

در این کد مانند مثال سوم، از آرگومان length در تابع SQL Substring خبری نیست. (LENGTH() را با length اشتباه نگیرید!) تابع ساب‌استرینگ در این کد، نخست به ردیف‌های ستون job_title مراجعه می‌کند. سپس مقدار نهایی حاصل از عملیات داخلی خود را به عنوان مقدار آرگومان start در نظر می‌گیرد و پاسخ را برمی‌گرداند. اما معنای عملیات مذکور در تابع SQL Substring چیست؟

LENGTH() طول هر رشته را در ستون job_title محاسبه می‌کند. REVERSE هر رشته موجود در این ستون را معکوس می‌کند. با معکوس شدن ترتیب کاراکترهای هر رشته، کلمه آخر در آن رشته به کلمه اول تبدیل می‌شود؛ یعنی همان کلمه‌ای که دنبالش هستیم. سپس POSITION() موقعیت اولین کاراکتر فاصله در رشته معکوس شده را پیدا می‌کند. نهایتاً مقدار برگردانده توسط POSITION() از مقدار برگردانده توسط LENGTH() کم شده و با دو جمع می‌شود.

نکته!

پس از انجام محاسبات فوق، موقعیت اولین کاراکتر مربوط به آخرین کلمه در هر یک از رشته‌های ستون job_title پیدا می‌شود. این مقدار به عنوان مقدار آرگومان start در تابع SQL Substring لحاظ می‌شود. حال با توجه به عدم وجود آرگومان length، زیررشته‌ای که آغازش برابر با مقدار start است و پایانش پایان کل رشته است، خوانده می‌شود. این زیررشته نهایتاً به عنوان خروجی تابع ساب‌استرینگ تحت ستون position در جدول زیر برگردانده می‌شود.