התחברות

התחברות
x
או
x
הרשמה
x

או

קליק אחד ואתם מחוברים. מהיר .קל .מאובטח.

מעניין? שתפו דף זה באמצעות הטלפון הנייד

מאמרים

מגמות של ביג דאטה בעולם הביטוח
CA Technologies
SSIS - Buffer Size Optimization
קטגוריה ראשית
בדיקות BI ו-DWH לעומת הבדיקות בתחומים אחרים
קטגוריה ראשית
איסוף דרישות לפרויקטי BI
קטגוריה ראשית
כח המידע במיקוד
קטגוריה ראשית
0

הדף שלנו בפייסבוק

ברוך הבא, אורח
שם משתמש: סיסמא: זכור אותי

דיון: טיפ - דוגמאות שימוש ב-REG_EXTRACT ו- REG_MATCH

טיפ - דוגמאות שימוש ב-REG_EXTRACT ו- REG_MATCH 9 years 7 months ago #6004

  • Or Pelach
  • Or Pelach's Avatar
  • Offline
  • Moderator
  • הודעות: 134
  • קרמה: -1
Examples of using REG_EXTRACT and REG_MATCH functions with differing telephone number formats
Examples of using REG_EXTRACT and REG_MATCH functions with differing telephone number formats
Solution:
The format of phone numbers in the source data can be in any of the following formats:

•(123) 456-7890 -H
•(123) 456-7890 -
•(123) 456-7890
•123-456-7890
•1234567890H
•1234567890

The following expressions check if the incoming string matches the format specified using REG_MATCH(). If it does match it uses REG_EXTRACT to extract parts of it and give the output formatted as 123-456-7890 –H or else it returns ‘NO MATCH’.
Phone Format : (123) 456-7890
Expression:
IIF(REG_MATCH(phone1_type,'[(](\d+)[)]\s(\d+)[-](\d+)'),
(REG_EXTRACT(phone1_type,'[(](\d+)[)]\s(\d+)[-](\d+)',1) || '-' || REG_EXTRACT(phone1_type,'[(](\d+)[)]\s(\d+)[-](\d+)',2) || '-' || REG_EXTRACT(phone1_type,'[(](\d+)[)]\s(\d+)[-](\d+)',3)),
'nomatch')
Phone Format : (123) 456-7890 -H
Expression:
IIF(REG_MATCH(phone1_type, '[(](\d+)[)]\s(\d+)[-](\d+)\s[-]\s(\w)'),
(REG_EXTRACT(phone1_type,'[(](\d+)[)]\s(\d+)[-](\d+)\s[-]\s(\w)',1) ||'-'|| REG_EXTRACT(phone1_type,'[(](\d+)[)]\s(\d+)[-](\d+)\s[-]\s(\w)',2) ||'-'|| REG_EXTRACT(phone1_type,'[(](\d+)[)]\s(\d+)[-](\d+)\s[-]\s(\w)',3) ||' -'|| REG_EXTRACT(phone1_type,'[(](\d+)[)]\s(\d+)[-](\d+)\s[-]\s(\w)',4)),
'nomatch')
Phone Format : (123) 456-7890 –
Expression:
IIF(REG_MATCH(phone1_type,'[(](\d+)[)]\s(\d+)[-](\d+)\s[-]'), (REG_EXTRACT(phone1_type,'[(](\d+)[)]\s(\d+)[-](\d+)\s[-]',1) || '-' || REG_EXTRACT(phone1_type,'[(](\d+)[)]\s(\d+)[-](\d+)\s[-]',2) || '-' || REG_EXTRACT(phone1_type,'[(](\d+)[)]\s(\d+)[-](\d+)\s[-]',3)), 'nomatch')
Phone Format : 1234567890
Expression:
IIF(REG_MATCH(phone1_type, '(\d\d\d)(\d\d\d)(\d\d\d\d)'), REG_EXTRACT(phone1_type, '(\d\d\d)(\d\d\d)(\d\d\d\d)',1) ||'-' || REG_EXTRACT(phone1_type, '(\d\d\d)(\d\d\d)(\d\d\d\d)',2) || '-' || REG_EXTRACT(phone1_type, '(\d\d\d)(\d\d\d)(\d\d\d\d)',3) , 'Nomatch')
Phone Format : 123-456-7890
Expression:
IIF(REG_MATCH(phone1_type,'\d\d\d[-]\d\d\d[-]\d\d\d\d'),phone1_type, 'nomatch')
Phone Format : 1234567890H
Expression:
IIF(REG_MATCH(phone1_type, '(\d\d\d)(\d\d\d)(\d\d\d\d)(\w)'), REG_EXTRACT(phone1_type, '(\d\d\d)(\d\d\d)(\d\d\d\d)(\w)',1) ||'-' || REG_EXTRACT(phone1_type, '(\d\d\d)(\d\d\d)(\d\d\d\d)(\w)',2) || '-' || REG_EXTRACT(phone1_type, '(\d\d\d)(\d\d\d)(\d\d\d\d)(\w)',3) || ' -' || REG_EXTRACT(phone1_type, '(\d\d\d)(\d\d\d)(\d\d\d\d)(\w)',4) , 'Nomatch')
Expression explanation:
REG_EXTRACT(phone1_type, '(\d\d\d)(\d\d\d)(\d\d\d\d)',1) ||'-' || REG_EXTRACT(phone1_type, '(\d\d\d)(\d\d\d)(\d\d\d\d)',2) || '-' || REG_EXTRACT(phone1_type, '(\d\d\d)(\d\d\d)(\d\d\d\d)',3)

If the phone number of source is 1234567890, REG_EXTRACT(phone1_type, '(\d\d\d)(\d\d\d)(\d\d\d\d)',1) will extract 123, REG _EXTRACT(phone1_type, '(\d\d\d)(\d\d\d)(\d\d\d\d)',2) will extract 456 and REG_EXTRACT(phone1_type, '(\d\d\d)(\d\d\d)(\d\d\d\d)',3) will extract 7890 according to the third argument specified for this function. i.e. the third argument states which sub-expression specified within () should be returned by the function.

Applies To
Product: PowerCenter
הנהלת האתר ביטלה גישת כתיבה ציבורית.
מנהלים: Laromme Barr
זמן יצירת העמוד: 0.263 שניות

Microsoft

Oracle

IBM

Informatica

Sap

SAS

Qlikview

Cloudera

Machine Learning