عبارات منظم (Regular expressions) که به اختصار به عنوان دستوران RegEx شناخته می شوند، یک الگو یا دنباله‌ای از کاراکترها هستند که به شما این امکان را می دهند یک داده از جنس رشته را از نظر میزان مطابقت بررسی کنید، آن را جستجو کنید و یا با مقدار دیگری جایگزین کنید. موارد زیادی وجود دارد که در حین تجزیه و تحلیل می‌خواهیم قسمت های خاصی از یک رشته را استخراج کنیم، بررسی کنیم که آیا یک رشته فرمت خاصی دارد یا نه، و حتی کاراکترهای خاصی را جایگزین یا حذف کنیم. توابع RegEx در بیگ کوئری، دستوراتی هستند که شما برای اجرای عملیات مختلف گفته شده به آنها نیاز دارید.
ترفندهای توابع RegEx در بیگ کوئری
قبل از اینکه نکات و ترفندهای جالب در مورد استفاده از عبارات منظم (RegEx) و توابع RegExp در BigQuery را برای استخراج و دستکاری داده‌هایتان توضیح دهم، باید داده‌های خود را وارد بیگ کوئری کنید. اگر با روش های ایمپورت داده در بیگ کوئری آشنایی ندارید، لطفاً روی لینک کلیک کنید.
| تابع | ساختار | توضیحات |
| REGEXP_CONTAINS | REGEXP_CONTAINS(value, regexp) | بررسی می کند که آیا ورودی با الگوی عبارت منظم داده شده مطابقت دارد یا خیر |
| REGEXP_EXTRACT | REGEXP_EXTRACT(value, regexp) | اولین عبارت منطبق با الگوی عبارت منظم مطابقت دارد را به عنوان خروجی باز می‌گرداند |
| REGEXP_EXTRACT_ALL | REGEXP_EXTRACT_ALL(value, regexp) | هر عبارتی که با الگوی عبارت منظم مطابقت دارد استخراج می کند. |
| REGEXP_INSTR | REGEXP_INSTR(source_value, regexp, [position], [occurrence], [occurrence_position]) | اولین موقعیتی که با الگوی تعیین شده مطابقت دارد، به عنوان خروجی باز می‌گرداند. |
| REGEXP_REPLACE | REGEXP_REPLACE(value, regexp, replacement) | بعد از پیدا کردن الگوی منطبق با ساختار تعیین شده، عبارت موجود در تابع را با عبارت قبلی جایگزین میکند. |
| REGEXP_SUBSTR | REGEXP_SUBSTR(value, regexp, [position], [occurrence]) | این تابع درواقع همان تابع REGEXP_EXTRACT است. |
نحوه بررسی درست بودن ساختار اطلاعات با استفاده از توابع RegEx
اولین ترفندی که در خصوص توابع RegEx در بیگ کوئری بررسی میکنیم این است که بررسی کنیم آیا ساختار تاریخ ایونتها در جداول آنالیتیکس در بیگ کوئری، صحیح هست یا نه. با فرض اینکه event_date به عنوان یک فیلد تاریخ در قالب YYYYMMDD ذخیره می شود، ما از تابع REGEXP_CONTAINS و عبارت منظم زیر استفاده خواهیم کرد:
^\d{8}$
- ^: شروع رشته را نشان می دهد.
- \d{8}: مقدار موجود در ستون دقیقا باید 8 رقم داشته باشد. (به دلیل فرمت YYYYMMDD )
- $: انتهای رشته را نشان می‌دهد.
پیدا کردن یک کلمه خاص با توابع RegEx در بیگ کوئری
تصور کنید که در جداول آنالیتیکس، به دنبال بازدید از صفحاتی هستیم که به عنوان مثال مربوط به دسته بندی آموزش آنالیتیکس 4 در سایت من هستند. ساختار urlها در سایت من به گونه ای است که عبارت /ga4/، آدرس‌هایی که مربوط به دسته بندی آموزش آنالیتیکس هست را نشان می‌دهند.
تشخیص عدم وجود چند کلمه در یک ستون در بیگ کوئری
از تابع [NOT] LIKE میتوان برای بررسی عدم وجود یک کلمه در یک ستون استفاده کرد. اما از توابع RegExp در بیگ کوئری میتوان برای بررسی عدم وجود چند کلمه و عدم حساسیت به حروف بزرگ یا کوچک استفاده کرد. دستوری که برای انجام این کار استفاده میکنیم، مشابه با دستور قبلی است، با این تفاوت که با اضافه کردن یک NOT، عدم وجود عبارت را بررسی میکنیم.
بررسی اینکه آیا یک ستون حاوی کلمه ای از ستون دیگر است یا خیر در بیگ کوئری
برای بررسی اینکه آیا یگ ستون حاوی کلمه ای از ستون دیگر است یا خیر، از تابع REGEXP_CONTAINS استفاده میکنیم. CONCAT یکی از پرکاربردترین دستوارت SQL در بیگ کوئری است که با استفاده از آن میتوان اطلاعات دو ستون را کنار یکدیگر قرار داد.
جایگزین یک عبارت در یک ستون با توابع RegExp در بیگ کوئری
یکی دیگر از کوئری‌های محبوب که در آن از عبارات منظم استفاده می شود، جایگزین کردن یک کلمه خاص با یک کلمه دیگر است. برای انجام این کار، از تابع REGEXP_REPLACE استفاده می کنیم و کلمه “ga4” را با کلمه “google-analytics-4” در مقدار page_location جایگزین می کنیم:
جدا کردن بخشی از استرینگ با توابع RegExp در بیگ کوئری
یکی دیگر از جذاب‌ترین کاربرد دستورات RegExp در بیگ کوئری این است که به شما این امکان را می‌دهند تا بخشی از یک رشته را انتخاب و استخراج کنید. با استفاده از تابع REGEXP_EXTRACT و این عبارت منظم r”^(.+?) “، می‌توانیم هر چیزی را که قبل از اولین space در یک رشته ظاهر می شود استخراج کنیم:
نحوه جایگزینی کاراکترهای خاص با دستورات RegExp
گاهی اوقات نیاز است که برخی از کاراکترهای خاص مانند نقطه، کاما، فاصله و… را حذف کنیم. برای انجام این کار می‌توانیم از دستور زیر استفاده کنیم:
نحوه استخراج یک عدد از یک رشته با دستورات RegExp
اگر می‌خواهید تمام اعداد موجود در یک رشته را پیدا کنید و/یا حتی آنها را جایگزین کنید، می توانید از دستوران RegExp استفاده کنید. برای انجام این کار، می‌توانیم از تابع REGEXP_EXTRACT_ALL به همراه عبارت منظم ([0-9]+) برای استخراج همه اعدادی که در یک آدرس ظاهر می‌شوند استفاده کنیم:
