Get Substring between two characters in SQL Server query

I want to get the string between two characters in the following string:

hello @user1, how are you?

I want to get the text between the @ and the SPACE after the @ which results in user1, is there a way to do this? There could even be 2 or more @ which I will need to extract like

hello @user1 ,@user2 ,@... , how are you?

here is a table-like example:

Sample	Excpected Output
Hello @user1 and @user2 how are you?	user1, user2
@user1 and @user2 replied	user1, user2

but there will always be a SPACE after each @.

Thanks for the help.

Answer

The usual way to approach would be a combination of String_Split() and String_Agg():

with sampledata as (
  select 'Hello @user1 and @user2 how are you?' sample union all
  select '@user1 and @user2 replied'
)
select sample, String_Agg(Replace([value], '@',''), ', ') Result
from sampledata
cross apply String_Split(sample,' ')
where [value] like '@%'
group by sample;

See Working fiddle

As requested, a possible alternative approach to ensure the ordering of the string elements is as follows; this method uses a json array that provides the array index for ordering:

select sample, 
  String_Agg(Replace([value], '@',''), ', ') within group(order by seq) Result
from sampledata
cross apply (
  select j.[value], 1 + Convert(tinyint, j.[key]) Seq 
  from OpenJson(Concat('["', replace(sample, ' ', '","'), '"]')) j
)s
where [value] like '@%'
group by sample;

Note from this alternative Fiddle you can reverse the ordering or the strings using the desc keyword.