Wildcard characters how to match several words?-CodePudding

   CASE      
      WHEN PhoneNumber LIKE '%[0-9]EXT[0-9]%' 
         THEN REPLACE(PhoneNumber, 'EXT', ' EXT ')
      WHEN PhoneNumber LIKE '%[0-9]EST[0-9]%' 
         THEN REPLACE(PhoneNumber, 'EST', ' EXT ')
      WHEN PhoneNumber LIKE '%[0-9]XTN[0-9]%' 
         THEN REPLACE(PhoneNumber, 'XTN', ' EXT ')
      WHEN PhoneNumber LIKE '%[0-9]XT[0-9]%' 
         THEN REPLACE(PhoneNumber, 'XT', ' EXT ')
      WHEN PhoneNumber LIKE '%[0-9]EX[0-9]%' 
         THEN REPLACE(PhoneNumber, 'EX', ' EXT ')
      WHEN PhoneNumber LIKE '%[0-9]X[0-9]%' 
         THEN REPLACE(PhoneNumber, 'X', ' EXT ')
      WHEN PhoneNumber LIKE '%[0-9]E[0-9]%' 
         THEN REPLACE(PhoneNumber, 'E', ' EXT ')

I have a bunch of WHEN conditions in the CASE clause. Is it possible to put them together in only 1 WHEN condition? Microsoft Azure SQL Data Warehouse only supports wildcard characters.

CodePudding user response：

Here is an option using Translate()

Declare @YourTable Table ([PhoneNumber] varchar(50))  Insert Into @YourTable Values 
 ('(555) 555-1212 Ext 25')
,('(555) 555-1212 Est 25')
,('(555) 555-1212 Ext25')
,('(555) 555-1212 Ex 25')
,('(555) 555-1212 E25')
,('(555) 555-1212 X25')
,('(555) 555-1212 # 25')
 

Select PhoneNumber
      ,replace(replace(replace(replace(
                              translate(PhoneNumber,'ABCDEFGHIJKLMNOPQRSTUVWXYZ#'
                                                   ,'###########################')
                              ,'#','†‡'),'‡†',''),'†‡','Ext ')
                              ,'  ', ' '
                              )
 From  @YourTable

Results

PhoneNumber             (No column name)
(555) 555-1212 Ext 25   (555) 555-1212 Ext 25
(555) 555-1212 Est 25   (555) 555-1212 Ext 25
(555) 555-1212 Ext25    (555) 555-1212 Ext 25
(555) 555-1212 Ex 25    (555) 555-1212 Ext 25
(555) 555-1212 E25      (555) 555-1212 Ext 25
(555) 555-1212 X25      (555) 555-1212 Ext 25
(555) 555-1212 # 25     (555) 555-1212 Ext 25