英文:
Split a string in SQL Server using regex and use the resulted array to populate many columns in a newly created table
问题
例如,我有这个字符串
'@38BARCLAYS BANK PURPOSE UK@33UK95UKDS7362637854896000 @1162398 @37 @89/FREQ/PAY BILL FH CONTR@23ACT NO. 094 FROM 02.12.2008 @11786234111 @23 @89 @11 @45ROAD'
我需要动态创建一个只有一行的表,其中包含所有这些列;而不是INSERT
,而是SELECT
。
银行名称:BARCLAYS BANK PURPOSE UK
银行代码:UK95UKDS7362637854896000
银行号:62398
银行账号:/FREQ/PAY BILL FH CONTR
合同号:CONTRACT NO. 094 FROM 02.12.2008,aso!
所以,基本上我需要替换所有以2位数字紧随其后的@实例,并使用这些列动态创建表格。从结果数组中,第一个索引,第一个列,第二个索引,第二个列,依此类推!
我使用的是SQL Server,而不是PostGreSql或Oracle。在这个环境中是否可能?我一直在努力处理这个问题,但一直无法解决,因为string_split
无法帮助,因为它只接受一个字符模式。我对SQL相当新手。非常感谢提前帮助!
另外,如果有办法可以在另一种语言中操作字符串,然后获取结果数组并填充该表,那也可以,或者如果有人知道SQL Server的regex_split_into_array用户定义函数,可以使用'@[0-9][0-9]'
模式而不仅仅是一个字符,就可以实现。与string_split提供的相同。非常感谢提前帮助!
正如我所说,我尝试使用string_split,但两个数字仍然保留,结果包含许多行而不是我需要的许多列
英文:
For instance, I have this string
'@38BARCLAYS BANK PURPOSE UK@33UK95UKDS7362637854896000 @1162398 @37 @89/FREQ/PAY BILL FH CONTR@23ACT NO. 094 FROM 02.12.2008 @11786234111 @23 @89 @11 @45ROAD'
And I need to dynamically create a table with only one row, with all these columns; INSERT
not SELECT
.
Bank Name: BARCLAYS BANK PURPOSE UK
BankCode: UK95UKDS7362637854896000
Bank No: 62398
BankAct: /FREQ/PAY BILL FH
ContractNo: CONTRACT NO. 094 FROM 02.12.2008, aso!
So, basically I need to replace all the @ followed by 2 digits instances and create the table dynamically with those columns. From the result array, first index, first column, second index, second column, and so on!
I'm using SQL Server, not using PostGreSql or Oracle. Is that even possible in this environment? I've been struggling a lot to manage this but couldn't manage, string_split
doesn't help since it takes only one char pattern. I'm pretty new to SQL. Thanks a lot in advance!
Also, if there is any way so I can manipulate the string in another language and then take the resulted array and populate that table, that should also work or if anyone knows a regexp_split_into_array user defined function for SQL Server as regex is mandatory here, that should also do it. Basically I'll need a function to use '@[0-9][0-9]' pattern instead of just one character as string_split provides.
Thanks a lot in advance!
As I said, I've tried using string_split but the two figures still stay and the result consists of many rows instead of many columns that I need
答案1
得分: 3
以下是使用JSON的一个选项(假设2016年及以后)。
注意:这假定分隔符是 @
,并且没有额外的 @
符号。
示例
Declare @S varchar(max) = '@38BARCLAYS BANK PURPOSE UK@33UK95UKDS7362637854896000 @1162398 @37 @89/FREQ/PAY BILL FH CONTR@23ACT NO. 094 FROM 02.12.2008 @11786234111 @23 @89 @11 @45ROAD'
Insert Into YourNewTable
Select BankName = stuff(JSON_VALUE(JS,'$[1]'),1,2,'')
,BankCode = stuff(JSON_VALUE(JS,'$[2]'),1,2,'')
,BankNo = stuff(JSON_VALUE(JS,'$[3]'),1,2,'')
,BankAct = stuff(JSON_VALUE(JS,'$[5]'),1,2,'')
,ContractNo = stuff(JSON_VALUE(JS,'$[6]'),1,2,'')
From (values ('["'+replace(string_escape(@S,'json'),'@','","','"')+'"]') ) B(JS)
Select * from YourNewTable
结果
BankName BankCode BankNo BankAct ContractNo
BARCLAYS BANK PURPOSE UK UK95UKDS7362637854896000 62398 /FREQ/PAY BILL FH CONTR ACT NO. 094 FROM 02.12.2008
英文:
Here is an option using JSON (assuming 2016+)
Note: This assumes the delimiter is the @
and there are no extra @'s
Example
Declare @S varchar(max) = '@38BARCLAYS BANK PURPOSE UK@33UK95UKDS7362637854896000 @1162398 @37 @89/FREQ/PAY BILL FH CONTR@23ACT NO. 094 FROM 02.12.2008 @11786234111 @23 @89 @11 @45ROAD'
Insert Into YourNewTable
Select BankName = stuff(JSON_VALUE(JS,'$[1]'),1,2,'')
,BankCode = stuff(JSON_VALUE(JS,'$[2]'),1,2,'')
,BankNo = stuff(JSON_VALUE(JS,'$[3]'),1,2,'')
,BankAct = stuff(JSON_VALUE(JS,'$[5]'),1,2,'')
,ContractNo = stuff(JSON_VALUE(JS,'$[6]'),1,2,'')
From (values ('["'+replace(string_escape(@S,'json'),'@','","')+'"]') ) B(JS)
Select * from YourNewTable
Results
BankName BankCode BankNo BankAct ContractNo
BARCLAYS BANK PURPOSE UK UK95UKDS7362637854896000 62398 /FREQ/PAY BILL FH CONTR ACT NO. 094 FROM 02.12.2008
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论