Extract two values between square brackets 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.-----

Is there 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 split/parse the string

Cross Apply C will create JSON to be consumed.

Example

Declare @YourTable Table ([Name] varchar(50))  Insert Into @YourTable Values 
 ('XCDRT [20.9 kd] qwer [12.234 r.t.]')
 
Select [Name]  = JSON_VALUE(JS,'$[0]')
      ,[Name1] = JSON_VALUE(JS,'$[1]')
      ,[Name2] = JSON_VALUE(JS,'$[2]')
 From  @YourTable A
 Cross Apply string_split([Name],']') B
 Cross Apply ( values ('["' replace(string_escape(trim(replace(B.Value,'[','')),'json'),' ','","') '"]') ) C(JS)
 Where B.value<>''

Results

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

CodePudding user response：

As an alternative solution, if you are on a bleeding edge version of the SQL Server data engine, then you make use of STRING_SPLIT and it's (new) ability to return the ordinal position of a value. Then, with some conditional aggregation, you can unpivot the results:

SELECT TRIM(MAX(CASE N.ordinal WHEN 1 THEN N.[value] END)) AS [Name],
       TRIM(MAX(CASE N.ordinal WHEN 2 THEN LEFT(N.[value], CHARINDEX(' ',N.[value]   ' ')) END)) AS [Name1],
       TRIM(MAX(CASE N.ordinal WHEN 2 THEN NULLIF(STUFF(N.[value], 1, CHARINDEX(' ',N.[value]   ' '),''),'') END)) AS [Name2]
FROM (VALUES('XCDRT [20.9 kd] qwer [12.234 r.t.]'))V([NAME])
     CROSS APPLY STRING_SPLIT(V.[NAME],']',1) R
     CROSS APPLY STRING_SPLIT(R.[value],'[',1) N
WHERE R.[value] != ''
GROUP BY V.[NAME],
         R.ordinal;

The TRIMs and NULLIF are there to "tidy" the values, as you'd have leading whitespace and incase you don't have a value for Name2.