在SQL Server查询中获得两个字符之间的子字符串

发布于 2025-02-10 10:22:04 字数 617 浏览 2 评论 0 原文

我想在以下字符串中获取两个字符之间的字符串：

hello @user1, how are you?

我想在@和 space 之间获取文本，@哪些导致 user1 ，有没有办法做到这一点？甚至可能有2个或更多 @我需要像

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

在这里提取的那样提取的是一个类似桌子的示例：

示例	删除的输出
helly @user1和 @user2你好吗？	user1，user2
@user1和 @user2回复了	user1，user2，

但是在每个 @之后，总会有一个 space 。

感谢您的帮助。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

筑梦 2025-02-17 10:22:04

通常的接近方法将是 string_split（）和 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;

请参见工作小提琴

根据要求，一种可能的替代方法来确保字符串元素的订购元素如下；此方法使用 json阵列提供订购阵列索引：

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;

注：此替代小提琴您可以使用 desc 关键字来逆转订购或字符串。

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.