如何根据Oracle SQL中的某些条件复制一行并拆分其中一列? [英] How do I copy a row and split one of the columns based on certain criteria in Oracle SQL?
问题描述
我有一张表，该表包含250k的数据，在该数据中，我有1000行，每个列中的一个引用列具有相同的数据，但不同.以下是一个示例:
I have a table of of 250k worth of data and out of this data I have 1000 rows that have the same data in every column bar one reference column which is different. The following is an example:
Name | Tel_No | Post_Code | Ref_No
Damian | 7900123456 | ME1 2BC | 12345678 1234567891234
Graeme | 7900789012 | ME1 2DE | 12 345 5678901234567
Sarah | 7900456789 | ME1 2FG | 90123456 890123456789
现在，我想对引用具有8位数字，然后是空格和13位数字的每一行进行操作，我想复制此行，但将ref_no拆分为一行， 8位数字ref_no和1和13位数字.
Now what I would like to do is for every row where the reference has an 8 digit number then a space and then a 13 digit number, I would like to copy this row but split the ref_no so that I have one row with the 8 digit ref_no and 1 with the 13 digit.
在上面的示例中，代码仅应与第一行一起使用，因为Graeme不符合此条件，因为ref_no有2位数字，然后有一个空格，因此代码应立即失败.对于Sarah，代码将失败，因为第二部分的ref_no只有12位数字而不是13位数字.对于Graeme和Sarah，它们的数据仍将保留在具有他们ref_no的单行中.
In the above example, the code should only work with the first line as Graeme doesn't meet this criteria as ref_no has 2 digits then a space therefore the code should fail straightaway. For Sarah the code will fail as the ref_no for the 2nd part only has 12 digits rather than 13. For Graeme and Sarah their data will still stay in the single rows with the ref_no they have.
我猜想必须使用正则表达式来找到匹配的模式，但是不幸的是，我还没有尝试过任何代码，因为它对我来说又是新的，而复制行则使我略有退缩之前曾做过类似的事情，因此希望能对最佳做法提出一些建议，以推动这一进展.
I am guessing this has got to be done with regular expressions to find a matching pattern, unfortunately I haven't attempted any code as yet as again its new to me and the copying of the row has threw me out slightly as not done something like this before hence would love some advice on the best practices to move this forward.
这就是表中的外观(我确实有一个自动ID列，因此新行也将分配有新ID):
Btw this is how it should look like in the table (I do have an auto ID column so the new rows will have new IDs assigned as well):
Name | Tel_No | Post_Code | Ref_No
Damian | 7900123456 | ME1 2BC | 12345678
Damian | 7900123456 | ME1 2BC | 1234567891234
谢谢.
编辑04/02/2020 -----------------------
EDIT 04/02/2020 -----------------------
抱歉，延误并不顺利.谢谢大家的答复. Yunnosch，Wiktor，Rob根据要求，以下是我的create table和insert语句，正是我想要的，只是为了使我的要求更加清楚.道歉应该从一开始就包含在内.
Apologies for the delay, unfortunately not been too well. Thanks guys for all your responses. Yunnosch, Wiktor, Rob as requested the following is my create table and insert statements and exactly what I am looking for just to make it a lot more clearer as to what I am asking. Apologies should have included at the start.
创建表:
CREATE TABLE "BU_TABLE"
( "ID" NUMBER NOT NULL ENABLE,
"NAME" VARCHAR2(255 BYTE),
"TEL_NO" VARCHAR2(255 BYTE),
"POST_CODE" VARCHAR2(8 BYTE),
"REF_NO" VARCHAR2(255 BYTE),
CONSTRAINT "TABLE1_PK" PRIMARY KEY ("ID")
;
我之所以将Tel_No和Ref_No列设置为Varchar2的原因是因为空格，因为当数字包含空格时，a)它将提示无效数字错误b)目前示例数据中没有Tel_No这个问题，但是随着添加更多数据，我可能会继续遇到这个问题.
The reason I have set the Tel_No and the Ref_No column as Varchar2 is because of the spaces as when numbers have spaces then a) it will prompt a Invalid Number error b) Tel_No at the moment with the example data doesn't have this issue but as more data is added I might have this issue going forward.
我的插入语句:
Insert into BU_TABLE (ID,NAME,TEL_NO,POST_CODE,REF_NO) values (1,'Damian','7900123456','ME1 2BC','12345678 1234567891234');
Insert into BU_TABLE (ID,NAME,TEL_NO,POST_CODE,REF_NO) values (2,'Graeme','7900789012','ME1 2DE','12 345 5678901234567');
Insert into BU_TABLE (ID,NAME,TEL_NO,POST_CODE,REF_NO) values (3,'Sarah','7900456789','ME1 2FG','90123456 890123456789');
现在我想让BU_TABLE与原始数据保持原样.我想在此表的后面创建另一个表以拆分Ref_No，因此在这里我有8个数字，一个空格和13个数字的序列.无论发生这种模式的地方，我都想复制该行，创建一个新的ID(下一个可用的)，然后给我以下内容:
Now what I would like is for the BU_TABLE to stay as it is, with the raw data. I would like to create another table of the back of this table to split out the Ref_No, so where I have a sequence of 8 numbers, a space and then 13 numbers. Wherever this pattern occurs I would like to duplicate the row, create a new ID (next available) and give me the following:
我想要的东西:
Name | Tel_No | Post_Code | Ref_No
Damian | 7900123456 | ME1 2BC | 12345678
Damian | 7900123456 | ME1 2BC | 1234567891234
所需代码未找到序列的行，数据行在表中将保持不变，因此我的最终表将如下所示:
The rows where the required code did not find the sequence, the rows of data will stay the same in the table so my final table will look like this:
最终表
ID | Name | Tel_No | Post_Code | Ref_No
2 | Graeme | 7900789012 | ME1 2DE | 12 345 5678901234567
3 | Sarah | 7900456789 | ME1 2FG | 90123456 890123456789
4 | Damian | 7900123456 | ME1 2BC | 12345678
5 | Damian | 7900123456 | ME1 2BC | 1234567891234
希望这使我想做的事情更加清楚.
Hopefully that makes it a lot more clearer as to what I am trying to do.
推荐答案
无论分割部分是否相等，都可以一起使用regexp_substr()和substr()函数进行相互比较.然后为满足where条件的每一行又生成一行:
You can use regexp_substr() and substr() functions together for mutual comparison whether the splitted parts are equal. And then generate one more row for each row satisfying the where condition :
with t as
(
select regexp_substr(Ref_No,'[[:digit:]]{8}+') as Ref_No1_0,
regexp_substr(Ref_No,'[^[:space:]]+$') as Ref_No2_0,
substr(Ref_No,1,8) as Ref_No1_1 , substr(Ref_No,-13) as Ref_No2_1,
t.*
from tab t
)
select Name , Tel_No , Post_Code ,
case when lvl = 1 then Ref_No1_0
when lvl = 2 then Ref_No2_0 end as Ref_No
from t
cross join (select level as lvl from dual connect by level <= 2)
where Ref_No1_0 = Ref_No1_1
and Ref_No2_0 = Ref_No2_1
或在where条件下直接使用regexp_like()(与substr()一起)功能:
Or directly use regexp_like()(together with substr()) functions in the where condition :
select Name , Tel_No , Post_Code ,
case when lvl = 1 then substr(Ref_No,1,8)
when lvl = 2 then substr(Ref_No,-13) end as Ref_No
from tab
cross join (select level as lvl from dual connect by level <= 2)
where regexp_like(substr(Ref_No,1,8),'[[:digit:]]{8}')
and regexp_like(substr(Ref_No,-13),'[[:digit:]]{13}')
这篇关于如何根据Oracle SQL中的某些条件复制一行并拆分其中一列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
