SQL query to remove the comma at the end with no characters beyond-CodePudding

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 ?

CodePudding user response：

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, ',$', '') 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.

CodePudding user response：

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>