SQL查询以删除逗号的最后，没有字符以外的字符

发布于 2025-02-05 15:39:29 字数 461 浏览 1 评论 0原文

我创建了一个查询来从表IRC_Table中获取输出，

Requistion_number                             Name
12                                       John Mayer, Andrew,
11                                       Swastak,

如果名称中的值最终具有逗号，那么它应该删除它 -

Requistion_number                             Name
12                                       John Mayer, Andrew
11                                       Swastak

哪个函数可以帮助我实现这一目标？

原文

I have created a query to get the output like from table IRC_TABLE-

Requistion_number                             Name
12                                       John Mayer, Andrew,
11                                       Swastak,

I want if the values in Name has comma at the end and nothing beyond then it should be removed-

Requistion_number                             Name
12                                       John Mayer, Andrew
11                                       Swastak

Which function will help me achieve this ?

分享到QQ

分享到微博

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

发布评论

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

沙与沫 2025-02-12 15:39:29

最简单，可能是最具性能的方法是使用trim：

SELECT Requistion_number, TRIM(TRAILING ',' FROM Name) AS Name
FROM yourTable;

您还可以使用regexp_replace在这里：

SELECT Requistion_number, REGEXP_REPLACE(Name, ',
如果替换逻辑为不仅仅是剥离某个最终角色，还要复杂。
, '') AS Name
FROM yourTable;

如果替换逻辑为不仅仅是剥离某个最终角色，还要复杂。

The easiest and probably most performant way to do this would be to use TRIM:

SELECT Requistion_number, TRIM(TRAILING ',' FROM Name) AS Name
FROM yourTable;

You could use also REGEXP_REPLACE here:

SELECT Requistion_number, REGEXP_REPLACE(Name, ',
The regex option would be of more value if the replacement logic were more complex than just stripping off a certain final character.
, '') AS Name
FROM yourTable;

The regex option would be of more value if the replacement logic were more complex than just stripping off a certain final character.

回复收藏 0 原文

紫竹語嫣☆ 2025-02-12 15:39:29

还有另一个选项（除了蒂姆已经说过的内容外）是rtrim（ right-trim ）函数。

当我们搜索各种选项时，即使substr case表达式可能会这样做（但是，嘿，您肯定不想使用它）：

SQL> select * From your_table;

REQUISTION_NUMBER NAME
----------------- -------------------
              122 John Mayer, Andrew,
              111 Swastak,
              333 No comma, here

SQL> select requistion_number,
  2         rtrim(name, ',') as name_1,
  3         substr(name, 1, length(name) - case when substr(name, -1) = ',' then 1
  4                                             else 0
  5                                        end) as name_2
  6  from your_table;

REQUISTION_NUMBER NAME_1              NAME_2
----------------- ------------------- -------------------
              122 John Mayer, Andrew  John Mayer, Andrew
              111 Swastak             Swastak
              333 No comma, here      No comma, here

SQL>

Yet another option (apart from what Tim already said) is the rtrim (right-trim) function.

When we're searching for various options, even substr with case expression might do (but hey, you surely will not want to use it):

SQL> select * From your_table;

REQUISTION_NUMBER NAME
----------------- -------------------
              122 John Mayer, Andrew,
              111 Swastak,
              333 No comma, here

SQL> select requistion_number,
  2         rtrim(name, ',') as name_1,
  3         substr(name, 1, length(name) - case when substr(name, -1) = ',' then 1
  4                                             else 0
  5                                        end) as name_2
  6  from your_table;

REQUISTION_NUMBER NAME_1              NAME_2
----------------- ------------------- -------------------
              122 John Mayer, Andrew  John Mayer, Andrew
              111 Swastak             Swastak
              333 No comma, here      No comma, here

SQL>

回复收藏 0 原文

~没有更多了~