Select particular value after first space

Question

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

Does [this](https://stackoverflow.com/questions/19449492/using-t-sql-return-nth-delimited-element-from-a-string) answer your question? — Zhorov, Aug 18 '22 at 10:12

score 3 · Answer 1 · answered Aug 18 '22 at 10:12

3

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;

Demo

answered Aug 18 '22 at 10:12

Tim Biegeleisen

502,043
27
286
360

Thanks for the response @Tim Biegeleisen. But I'm not getting the ouput, Here is the error: **Invalid length parameter passed to the LEFT or SUBSTRING function.** – pdc Aug 18 '22 at 10:27
1

Well we _don't_ get this error from your sample data, implying that you have some _other_ data which is causing this. My guess is that there is one or more `HID_Value` values which do not have three words in them (i.e. at least 2 spaces). Check your data for this problem. – Tim Biegeleisen Aug 18 '22 at 10:36
@pd You could add a "Fail-Safe" on the 2nd charindex by simply adding a space ... HID_Value+' ' – John Cappelletti Aug 18 '22 at 11:55

John Cappelletti · Answer 2 · 2022-08-23T18:35:00.420

Just another option using a bit of JSON

Select HID_Value
      ,NewVal = JSON_VALUE('["'+replace(string_escape(HID_Value,'json'),' ','","')+'"]' ,'$[1]')
 From YourTable

Results

For something 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

Select particular value after first space

2 Answers2

Demo