Home » SQL & PL/SQL » SQL & PL/SQL » Create comma delimited function (19.2)
Create comma delimited function [message #686325] Wed, 27 July 2022 14:39 Go to next message
Unclefool
Messages: 65
Registered: August 2021
Member
I would like to remove spaces and add commas in between the data using SQL. Like if i have column with the data ' mon tue wed thu ' i need it to be like 'mon,tue,wed,thu'

I got the following working but can this be converted into a generic function where I can call it on any column, like value1, value2…?


CREATE TABLE table_name (value) AS
SELECT ' mon tue wed thu ' FROM DUAL

SELECT TRIM(BOTH ',' FROM REGEXP_REPLACE(value, '\s+', ',')) AS replaced_value
FROM   table_name;

Re: Create comma delimited function [message #686326 is a reply to message #686325] Wed, 27 July 2022 15:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68342
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not really clear.
What does take the function?
What does it return?
Post an example.

Re: Create comma delimited function [message #686327 is a reply to message #686326] Wed, 27 July 2022 16:14 Go to previous messageGo to next message
Unclefool
Messages: 65
Registered: August 2021
Member
I want to pass in something like a,sentence. Remove the spaces between the words. In place of the spaces put in commas
Re: Create comma delimited function [message #686328 is a reply to message #686327] Thu, 28 July 2022 00:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68342
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So you have the solution, it is the one you posted.

Re: Create comma delimited function [message #686329 is a reply to message #686328] Thu, 28 July 2022 07:38 Go to previous messageGo to next message
Littlefoot
Messages: 21756
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just "convert" that SELECT statement into a function:

SQL> CREATE OR REPLACE FUNCTION f_space_to_comma (par_string IN VARCHAR2)
  2     RETURN VARCHAR2
  3  IS
  4  BEGIN
  5     RETURN TRIM (BOTH ',' FROM REGEXP_REPLACE (par_string, '\s+', ','));
  6  END;
  7  /

Function created.

Then call it wherever you want, e.g.

SQL> select f_space_to_comma(' mon tue wed thu ') result
  2  from dual;

RESULT
--------------------------------------------------------------------------------
mon,tue,wed,thu

SQL> CREATE TABLE table_name (value) AS
  2  SELECT ' mon tue wed thu ' FROM DUAL;

Table created.

SQL> select f_space_to_comma(value) result
  2  from table_name;

RESULT
--------------------------------------------------------------------------------
mon,tue,wed,thu

SQL>
Re: Create comma delimited function [message #686330 is a reply to message #686329] Thu, 28 July 2022 08:31 Go to previous message
Michel Cadot
Messages: 68342
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You give him no chance to learn and encourage his laziness, with his first post and previous topic (with no feedback by the way) he had the way to do it himself. Sad

Tell me and I'll forget; show me and I may remember; involve me and I'll understand

[Updated on: Thu, 28 July 2022 11:46]

Report message to a moderator

Previous Topic: Problem in partition exchange
Next Topic: Audit Database Problem
Goto Forum:
  


Current Time: Mon Jan 30 20:59:27 CST 2023