Select particular value after first space-CodePudding

I have a table and it has only one column(HID_Value). The value in the column is separated by space . Now all I want is to get the second value after the first Space.

HID_Value
AHIT 6 08132022 Exp Reimburse 08/13/2020 0.00 1
APHB 601 08132022 Exp Reimburse 08/13/2020 0.00 1
ATCK 12 08132022 Exp Reimburse 08/13/2020 0.00 1
ARMB 34 08132022 Exp Reimburse 08/13/2020 0.00 1
APLB 5 1 1 5 970515- 900.08000. 144 MS 1 86.88
APLB 5 2 1 5 970515- 900.08000. 144 MS 1 36.25
APLB 5 3 1 5 970515- 900.08000. 144 MS 1 36.25

I need the output as below

I tried the below query, but couldn't able to get the expected results.

select SUBSTRING(HID_Value,5,charindex(' ',HID_Value) 1) from table

CodePudding user response：

You may use CHARINDEX twice here, once to find the position of the first space, and the other time to find the position of the second space. Then, just take a substring of the column value using those two space positions and an offset.

SELECT SUBSTRING(HID_Value,
                 CHARINDEX(' ', HID_Value)   1,
                 CHARINDEX(' ', HID_Value, CHARINDEX(' ', HID_Value)   1) -
                     CHARINDEX(' ', HID_Value))
FROM yourTable;

For some a bit more robust, you can use a CROSS APPLY

Select A.HID_Value
      ,Pos1 = JSON_VALUE(JS,'$[0]')
      ,Pos2 = JSON_VALUE(JS,'$[1]')
      ,Pos3 = JSON_VALUE(JS,'$[2]')
      ,Pos4 = JSON_VALUE(JS,'$[3]')
 From  YourTable A
 Cross Apply (values ( '["' replace(string_escape(HID_Value,'json'),' ','","') '"]' ) ) B(JS)

Results