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:

but there will always be a SPACE after each @.

Thanks for the help.

CodePudding user response：

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