How can I extract two values between square bracklets an get the results in two different columns?-CodePudding

I have in a column some Names and then square brackets with some numbers and letters inside. How can I extract two values between square brackets and get the results in two different columns?

I start from the Column 'NAME' with the value 'XCDRT [20.9 kd]'

--NAME--

XCDRT [20.9 kd] qwer [12.234 r.t.]

and I would like to get 3 columns with the values in different columns

-- NAME--- NAME 1--- NAME 2---

--XCDRT---- 20.9-------- kd---- --qwer----- 12.234-------- r.t.-----

Do you have a function for such a problem?

I tried to split the value but I don't get the results that I need.

CodePudding user response：

With a bit of JSON and a CROSS APPLY (or two)

Cross Apply B will convert the row to JSON.

Cross Apply C will parse the individual values/rows

Example

Declare @YourTable Table ([Name] varchar(50))  Insert Into @YourTable Values 
 ('XCDRT [20.9 kd] qwer [12.234 r.t.]')
 
 Select C.*
 From  @YourTable A
 Cross Apply ( values  ('["' replace(string_escape(replace(replace(A.[Name],'[',''),']',''),'json'),' ','","') '"]') ) B(JS)
 Cross Apply ( values  (JSON_VALUE(JS,'$[0]'),JSON_VALUE(JS,'$[1]'),JSON_VALUE(JS,'$[2]') )
                      ,(JSON_VALUE(JS,'$[3]'),JSON_VALUE(JS,'$[4]'),JSON_VALUE(JS,'$[5]') )
             ) C ([Name],[Name1],[Name2])

Results

Name    Name1   Name2
XCDRT   20.9    kd
qwer    12.234  r.t.

CodePudding user response：

You can use the SUBSTRING() and CHARINDEX() functions along with the CROSS APPLY function in SQL Server to extract the values between square brackets and split them into separate columns.

Here is an example of how you can do this in SQL:

WITH CTE AS (
    SELECT 
       name,
       CAST('<X>'   REPLACE(name, '[', '</X><X>')   '</X>' AS XML) as name_xml
    FROM my_table
)

SELECT
    name,
    x.value('/X[1]','nvarchar(max)') as name1,
    x.value('/X[2]','nvarchar(max)') as name2

FROM CTE
CROSS APPLY name_xml.nodes('/X') as y(x);

In this example, I use CTE to select the name column and cast it as an XML. Then I use the REPLACE() function to replace the square brackets with xml tags. After that, I use the CROSS APPLY function to extract the values between the xml tags, resulting in name1 and name2 columns.

This is just one way to solve the problem, depending on your data and requirements you might have to tweak it a bit to fit your needs.