آموزش هوش تجاری از ۰ تا ۱۰۰

با داده های متنی در SQL کار می کنید؟ نحوه بدست آوردن مقادیر از هر نقطه در رشته را توضیح می دهیم.

وقتی به کار با داده ها در SQL فکر می کنید، احتمالاً اولین فکر شما یک پایگاه داده پر از اعداد و کد SQL شما است که محاسبات بسیار زیبایی را انجام می دهد. اما متن نیز داده است! یافتن داده های متنی در پایگاه داده بسیار رایج است. نه تنها باید آن را استخراج کنید، بلکه اغلب باید آن را دستکاری کنید. توابعی که به شما اجازه انجام این کار را می دهند، توابع متنی نامیده می شوند.

آنها یک موضوع ضروری برای هر کسی که SQL یاد می گیرد یا استفاده می کند. توابع متن، همراه با توابع عددی و تاریخ و زمان، تمرکز اصلی دوره تعاملی توابع استاندارد SQL ما هستند. علاوه بر این، به شما می آموزد که چگونه با مقادیر NULL در پایگاه داده ها رفتار کنید، داده ها را جمع آوری کنید و از CASE WHEN استفاده کنید. برای انجام 211 تمرین تعاملی که دوره ارائه می دهد فقط به اتصال به اینترنت و درک اولیه SQL نیاز دارید.

اگر دوست دارید SQL را با استفاده از تمرینات عملی یاد بگیرید، باید LearnSQL.com را امتحان کنید.

یکی از توابع متنی رایجی که دوره پوشش می دهد SUBSTRING () است. در این مقاله، پنج مثال کسب و کار واقعی داریم که کاربردهای اصلی این تابع را پوشش می دهد. اگر با توابع متن آشنایی ندارید، ممکن است برخی از مثال‌ها پیچیده به نظر برسند، بنابراین مطمئن شوید که برگه تقلب استاندارد SQL Functions یا یک نمای کلی از توابع متنی SQL را در کنار خود داشته باشید.

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

SUBSTRING (بیان، شروع، طول)
برای آرگومان عبارت، یک رشته به معنای واقعی کلمه می نویسید یا ستونی را مشخص می کنید که می خواهید رشته فرعی را از آن استخراج کنید. آرگومان start یک عدد صحیح است که موقعیت عددی کاراکتر را در رشته ای که رشته فرعی شروع می شود نشان می دهد. آرگومان length، همانطور که از نامش می‌گوید، طول، یک مقدار صحیح، رشته فرعی را که باید برگردانده شود را تعریف می‌کند.

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

اینطوری کار میکنه:

تابع زیر رشته sql
در رشته بالا، رشته فرعی که از موقعیت 1 شروع می شود و طول آن سه کاراکتر است "STR" است.

اکنون که اصول را پوشش داده ایم، اجازه دهید چندین نمونه را به شما نشان دهم. شروع، البته، با ساده ترین!

مثال 1: زیر رشته از یک رشته تحت اللفظی
تابع ()SUBSTRING یک رشته فرعی را از هر رشته ای که می خواهید برمی گرداند. شما می توانید رشته را به صراحت به عنوان یک آرگومان بنویسید، مانند این:

SELECT SUBSTRING('This is the first substring مثال', 9, 10) AS substring_extraction;
این به این معنی است: من می خواهم یک زیر رشته از متن "This is the first substring example" پیدا کنم. آرگومان ها می گویند که رشته فرعی از نهمین کاراکتر رشته شروع می شود و طول آن 10 کاراکتر است.

بیایید ببینیم این کد چه چیزی را برمی گرداند:

substring_extraction
اولین
یک ستون و یک ردیف وجود دارد. زیررشته استخراج شده "اولین" است. این ابتدایی ترین کاربرد SUBSTRING(); کد حتی از هیچ جدولی استفاده نمی کند!

جدول کارمندان
برای نشان دادن مثال‌های جالب‌تر، به اطلاعاتی نیاز دارم. بگذارید جدولی به نام کارکنان را به شما معرفی کنم.

این جدول اطلاعات مربوط به کارمندان یک شرکت خیالی کولر را در ستون های زیر ذخیره می کند:

شناسه - شناسه کارمند.
first_name - نام کوچک کارمند.
last_name - نام خانوادگی کارمند.
ایمیل - ایمیل کارمند.
job_title - عنوان شغلی کارمند.
بخش - بخش کارمند.
start_date - تاریخ شروع کار کارمند در کولر.
در اینجا چندین ردیف اول وجود دارد تا بتوانید درک درستی از داده ها داشته باشید:

شناسه first_name last_name ایمیل job_title بخش start_date
1 کلارنس ویلکینسون cwilkinson@kooler.com دستیار فروش جونیور فروش 09/2021
2 میراندا براون mbrown@kooler.com کارشناس ارشد فروش فروش 01/2020
3 فرانک دربین fdrebin@kooler.com مدیر فروش جوان 2019/08
مثال 2: زیر رشته از یک ستون
همانطور که می توانید تصور کنید، نوشتن عبارت رشته ای به طور صریح تنها راه استفاده از SUBSTRING () نیست. شما همچنین می توانید آن را در یک ستون از یک جدول استفاده کنید.

در اینجا یکی از این نمونه ها وجود دارد. من می خواهم حروف اول همه کارمندان را پیدا کنم. من از ستون ایمیل استفاده می کنم زیرا می دانم که دو حرف اول آدرس ایمیل حروف اول هستند:

نام_نام را انتخاب کنید،
     نام خانوادگی،
     پست الکترونیک،
     SUBSTRING(ایمیل، 1، 2) AS working_initials
از کارکنان؛
ایمیل ستون را در تابع مشخص می کنم. دریافت دو حرف اول از آدرس ایمیل به این معنی است که رشته فرعی از اولین کاراکتر به طول دو کاراکتر شروع می شود. این نتیجه مطلوب را برمی گرداند:

first_name last_name ایمیل staff_initials
کلارنس ویلکینسون cwilkinson@kooler.com cw
میراندا براون mbrown@kooler.com mb
فرانک دربین fdrebin@kooler.com fd
ویوین کلی vkelly@kooler.com vk
استیو استفنز sstephens@kooler.com ss
Nastassja Harrison nharrison@kooler.com nh
توماس پترسون tpeterson@kooler.com tp
ماتیلد کینسکی mkinski@kooler.com mk
Mateusz Wozniak mwozniak@kooler.com mw
آگهی آینه دویل adoyle@kooler.com
لورنزو آلفیری aalfieri@kooler.com aa
پترا بابیچ pbabic@kooler.com pb
Duarte Simoes dsimoes@kooler.com ds
اولنا کوستنکو okostenko@kooler.com خوب
Laurens Grotenhuis lgrotenhuis@kooler.com lg
مثال 3: رشته فرعی بدون آرگومان طول

می توانید آرگومان length را در SUBSTRING() حذف کنید و تابع همچنان کار می کند. یک مثال خوب زمانی است که می خواهید فقط سال تاریخ شروع استخدام را نشان دهید. ببینید، ستون start_date برای آن کمی غیر دوستانه است. این تاریخ به عنوان داده متنی در قالب MM/YYYY نوشته می شود.

خوشبختانه SUBSTRING() این مشکل را حل می کند:

نام_نام را انتخاب کنید،
     نام خانوادگی،
     تاریخ شروع،
     SUBSTRING (تاریخ_شروع، 4) AS شروع_سال
از کارکنان؛
برای دریافت سال از ستون start_date، تعریف شروع رشته فرعی کافی است. در این کد زیر رشته از کاراکتر چهارم شروع می شود. از آنجایی که من آرگومان length را حذف می‌کنم، طول رشته فرعی هر چند تا انتهای رشته از کاراکتر چهارم طولانی باشد. همانطور که در زیر می بینید، من به راحتی سال را به این ترتیب دریافت می کنم:

first_name last_name start_date start_year
کلارنس ویلکینسون 09/2021 2021
میراندا براون 01/2020 2020
فرانک دربین 08/2019 2019
ویوین کلی 03/2019 2019
استیو استفنز 07/2021 2021
Nastassja Harrison 03/2022 2022
توماس پترسون 01/2022 2022
ماتیلد کینسکی 01/2022 2022
Mateusz Wozniak 01/2022 2022
آینه دویل 10/2021 2021
لورنزو آلفیری 10/2021 2021
پترا بابیچ 05/2021 2021
Duarte Simoes 04/2020 2020
اولنا کوستنکو 11/2019 2019
Laurens Grotenhuis 06/2017 2017
مثال 4: POSITION() و CHARINDEX()
بازگشت به کار با ایمیل. طبق خط‌مشی شرکت، نقطه محلی یک آدرس ایمیل (یعنی قسمت قبل از «@») همچنین نام کاربری کارمند برای ورود به همه برنامه‌های تجاری است. شما باید این نام کاربری را استخراج کنید. در اینجا چگونه است:

نام_نام را انتخاب کنید،
       نام خانوادگی،
     SUBSTRING (ایمیل، 1، POSITION('@' در ایمیل)-1) به عنوان نام کاربری
از کارکنان؛
دو استدلال اول همان چیزی است که قبلاً دیده اید. من می خواهم یک زیر رشته را از ایمیل ستون استخراج کنم و می خواهم از اولین کاراکتر رشته شروع شود. اما اکنون، طول رشته فرعی برای هر کارمند متفاوت است. چگونه به تابع بگویم همه کاراکترها را قبل از علامت «@» برگرداند؟

بهترین مکان برای تسلط بر توابع SQL دوره تعاملی استاندارد توابع SQL ما است.

من از POSITION() که معادل CHARINDEX() در SQL Server یا MySQL است استفاده می کنم. کاراکتر مشخص شده را در رشته قرار می دهد و موقعیت کاراکتر عددی آن را برمی گرداند. بنابراین، طول رشته فرعی که نام کاربری کارمند است برابر با POSITION('@' IN email) -1 است. چرا منهای یک؟ چون نمی‌خواهم «@» در نام کاربری کارمند درج شود.

این هم نتیجه:

first_name last_name نام کاربری
کلارنس ویلکینسون کویلکینسون
میراندا براون قهوه ای
فرانک دربین fdrebin
ویوین کلی ویکلی
استیو استفنز استفنز
Nastassja Harrison nharrison
توماس پترسون پترسون
ماتیلد کینسکی مکینسکی
Mateusz Wozniak mwozniak
آینه دویل آدویل
لورنزو آلفیری آلفیری
پترا بابیچ pbabic
Duarte Simoes dsimoes
اولنا کوستنکو اوکوستنکو
Laurens Grotenhuis lgrotenhuis
مثال 5: LENGTH() + POSITION()

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

با استفاده از SQL، می توانم این را به عنوان زیر رشته استخراج کنم:

نام_نام را انتخاب کنید،
       نام خانوادگی،
       عنوان_کار،
       SUBSTRING(عنوان_شغل، LENGTH(عنوان_شغل) - POSITION(' ' در REVERSE(عنوان_شغل))+2) AS
از کارکنان؛
این مثال دیگری از حذف آرگومان طول است، البته کمی پیچیده تر. مثل همیشه، ابتدا ستون رشته – job_title را در این مورد مشخص می کنم. پس از آن، من به نحوی نیاز دارم که زیر رشته ای را پیدا کنم که فقط از آخرین کلمه در عنوان شغل تشکیل شده باشد.

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

این کمی پیچیده است زیرا نام موقعیت های شغلی مختلف طول های متفاوتی دارد. تنها چیزی که کلمات را از هم جدا می کند فضای خالی است. بنابراین، برای به دست آوردن طول کلمه سوم در رشته، باید تعداد کاراکترها را تا فضای خالی بشمارم، اما از سمت راست.

تابع POSITION () روز را دوباره ذخیره می کند، اما این بار با ()REVERSE ترکیب شده است. تابع ()REVERSE عبارت رشته را معکوس می‌کند تا «دستیار فروش جوان» به «tnatsissA selaS roinuJ» تبدیل شود. حرف آخر تبدیل به حرف اول می شود. خود کلمه نیز معکوس است، اما اینجا مهم نیست.

POSITION() موقعیت فضای خالی را بعد از اولین کلمه رشته معکوس پیدا می کند. این برابر با جای خالی قبل از آخرین کلمه در رشته اصلی (غیر معکوس) است.

اوه! حالا اگر این عدد را از طول کل رشته اصلی کم کنم، شروع رشته فرعی را دریافت می کنم، درست است؟

خوب، نه کاملا! با استفاده از این تفاوت، زیر رشته ای تولید می شود که شامل آخرین حرف کلمه دوم و فضای خالی قبل از آخرین کلمه است. چرا اینطور است؟

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

first_name last_name job_title موقعیت
دستیار فروش کلارنس ویلکینسون
میراندا براون کارشناس ارشد فروش
فرانک دربین مدیر فروش جوان
Vivien Kelly مدیر ارشد فروش
استیو استفنز، کارشناس فروش جوان
ناستاسیا هریسون، کارشناس فروش جوان
توماس پترسون، کارشناس گزارشگری جوان
ماتیلد کینسکی تحلیلگر گزارشگری جوان
Mateusz Wozniak کارشناس ارشد گزارشگری
آینه دویل، مدیر گزارشگری جوان
لورنزو آلفیری مدیر ارشد گزارشگری
پترا بابیچ جونیور دستیار منابع انسانی
Duarte Simoes دستیار HR Junior
اولنا کوستنکو، دستیار ارشد منابع انسانی
Laurens Grotenhuis مدیر ارشد مدیریت منابع انسانی
اکنون که من چند توابع دیگر را معرفی کردم، ممکن است بخواهید به برخی از توابع متنی دیگر که ممکن است برای شما مفید باشند نگاهی بیندازید.

درباره SUBSTRING () و کار با داده های متنی بیشتر بیاموزید
اکنون می دانید که چه زمانی و چگونه از SUBSTRING() استفاده کنید. وقت تمرین است!

آیا از قبل SQL را می دانید اما نمی دانید با آن چه کار کنید؟ دوره SQL Practice Set تعاملی ما را بررسی کنید!

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

تا زمانی که مطمئن نیستید که آیا دوره برای شما مناسب است؟ در اینجا شرح مفصلی از آنچه این دوره پوشش می دهد و چگونه به شما کمک می کند ارائه شده است.