sql - 如何在oracle中分隔包含数据varchar和数字和日期的字段

Question

如何在oracle中分隔包含数据var char和数字和日期的字段
例如:

table1:
column1:MohammadBadarneh_07889855_12021990

create procedure to separate and insert into table2:
table2:
name:Mohammad Badarneh
phone number: 07889855
date: 12-02-1990 

Answer 1

例如，要在不验证日期格式的情况下分离字符串值，您可以编写一个类似的查询。

-- sample of data
 with t1(col) as(
   select 'MohammadBadarneh_07889855_12021990'       from dual union all
   select 'SomethingElse_07889855_12021990'          from dual union all
   select 'SomethingDifferentHere_07889855_12021990' from dual union all
   select 'MohammadBadarneh_07869855_11021990'       from dual
  ),
  upos (ps) as(
    select level as ps
      from dual
   connect by level <= 3
  ), 
  PData as(
    select Name
         , Phone
         , to_date(Date1, 'dd-mm-yyyy') date1
      from (select q.*
                 , row_number() over(partition by ps order by ps) rn
              from (select u.ps 
                         , regexp_substr(col, '[^_]+', 1, u.ps) res 
                      from t1
                     cross join upos u
                   ) q
           ) s
     pivot (
       min(res)
       for ps in (1 as Name,2 Phone,3 Date1)
     )
  )

select Name
     , Phone
     , Date1
  from PData

结果:

Name                    Phone         Date1 
---------------------------------------------
MohammadBadarneh        07889855     12-02-1990 
SomethingElse           07889855     12-02-1990 
MohammadBadarneh        07889855     11-02-1990 
SomethingDifferentHere  07869855     12-02-1990 

这是一个 DEMO

那么你可以使用 insert将上述查询的输出插入到表中的语句:

   insert into your_table_name
      select Name
           , Phone
           , to_date(Date1, 'dd-mm-yyyy')
        from (select q.*
                   , row_number() over(partition by ps order by ps) rn
                from (select u.ps 
                           , regexp_substr(t.your_string_column, '[^_]+', 1, u.ps) res 
                        from Yor_Table_With_String t
                       cross join (select level as ps
                                     from dual
                                  connect by level <= 3
                                  ) u
                     ) q
             ) s
       pivot (
         min(res)
         for ps in (1 as Name,2 Phone,3 Date1)
        )