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

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