How can i display a string adding a hyphen each word on PL/SQL-CodePudding

How can I display a string, separating each letter by a dash with a for loop?

For example i want to display:

h-e-l-l-o-w-o-r-l-d

I tried with the substr function but I can't get it out

CodePudding user response：

If it must be PL/SQL and FOR loop, then you could

SQL> set serveroutput on
SQL> declare
  2    l_str  varchar2(20) := 'helloworld';
  3    retval varchar2(50);
  4  begin
  5    for i in 1 .. length(l_str) loop
  6      retval := retval || substr(l_str, i, 1) ||'-';
  7    end loop;
  8    retval := rtrim(retval, '-');
  9    dbms_output.put_line(retval);
 10  end;
 11  /
h-e-l-l-o-w-o-r-l-d

PL/SQL procedure successfully completed.

SQL>

Otherwise, consider e.g.

SQL> select rtrim(regexp_replace('helloworld', '(.)', '\1-'), '-') result from dual;

RESULT
-------------------
h-e-l-l-o-w-o-r-l-d

SQL>

SQL> select listagg(substr('helloworld', level, 1), '-') within group (order by level) result
  2  from dual
  3  connect by level <= length('helloworld');

RESULT
--------------------------------------------------------------------------------
h-e-l-l-o-w-o-r-l-d

SQL>

CodePudding user response：

You can just output each successive character and, after the first, output a hyphen between them:

DECLARE
  string VARCHAR2(20) := 'helloworld';
BEGIN
  DBMS_OUTPUT.PUT(SUBSTR(string, 1, 1));
  FOR i IN 2 .. LENGTH(string)
  LOOP
    DBMS_OUTPUT.PUT('-');
    DBMS_OUTPUT.PUT(SUBSTR(string, i, 1));
  END LOOP;
  DBMS_OUTPUT.NEW_LINE();
END;
/

Which outputs:

h-e-l-l-o-w-o-r-l-d

If you want to do it without loops then you can use:

DECLARE
  string VARCHAR2(20) := 'helloworld';
BEGIN
  DBMS_OUTPUT.PUT_LINE( SUBSTR(REGEXP_REPLACE(string, '(.)', '-\1'), 2) );
END;
/

You should not use LTRIM (or RTRIM) to remove the hyphens as, if the input string has leading (or trailing) hyphens then these would be removed from the output and that would be erroneous.

For example:

DECLARE
  string VARCHAR2(20) := '--helloworld--';
BEGIN
  DBMS_OUTPUT.PUT_LINE('Correct:');
  DBMS_OUTPUT.PUT_LINE(SUBSTR(REGEXP_REPLACE(string, '(.)', '-\1'), 2));
  DBMS_OUTPUT.PUT_LINE('Incorrect:');
  DBMS_OUTPUT.PUT_LINE(LTRIM(REGEXP_REPLACE(string, '(.)', '-\1'), '-'));
END;
/

Outputs:

Correct:
----h-e-l-l-o-w-o-r-l-d----
Incorrect:
h-e-l-l-o-w-o-r-l-d----

db<>fiddle here