Search code examples
sql-serverregexregex-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 :(

Solution

  • 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