-1

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 ?

MT0
  • 143,790
  • 11
  • 59
  • 117
SSA_Tech124
  • 577
  • 1
  • 9
  • 25

2 Answers2

2

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

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>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • More interesting than this strange case when would be if there is a simple query that works on every DB and not on Oracle DB's only. Maybe you have a idea how to do this? Since these substring and trim functions are very different between the different DB's, it seems this is not possible on a simple way? – Jonas Metzler Jun 09 '22 at 06:20
  • @Jonas, as the OP apparently uses Oracle (BI Publisher is Oracle's reporting tool; SQL Developer *mostly* runs against Oracle databases), I posted code that works on Oracle. I don't use other databases so I can't really tell whether it will (or will not) work elsewhere. Most databases use *similar* functions, but there most probably are some differences. Therefore, I don't know which code (if there is any at all) would work on EVERY database. – Littlefoot Jun 09 '22 at 06:29
  • Sure, the question of the OP is already answered by Tim. It was just an idea of thinking outside the box how to do this for each DB. But to be honest, I don't have a good idea for that, too. – Jonas Metzler Jun 09 '22 at 06:32