Split a string in SQL Server using regex and use the resulted array to populate many columns in a newly created table

huangapple go评论61阅读模式
英文:

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,但两个数字仍然保留,结果包含许多行而不是我需要的许多列 Split a string in SQL Server using regex and use the resulted array to populate many columns in a newly created table

英文:

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 Split a string in SQL Server using regex and use the resulted array to populate many columns in a newly created table

答案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 

huangapple
  • 本文由 发表于 2023年7月23日 20:44:56
  • 转载请务必保留本文链接:https://go.coder-hub.com/76748316.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定