CODEWITHSUNDEEP
oracle-databaseplsql
ReneJanssen
ReneJanssen

Reputation: 25

Insert space in a string x characters oracle

declare 
  v_nr number DEFAULT 12345678944;    
  v_numberWithSpace varchar2(255);    
  v_count number DEFAULT 1;    
begin    
  for i in 1..length(v_nr)  loop    
    v_numberWithSpace := v_numberWithSpace ||substr(v_nr, v_count, 4)|| ' .';    
    v_count := v_count + 4;
    DBMS_OUTPUT.PUT_LINE( v_numberWithSpace);    
  end loop;      
  v_numberWithSpace := substr(v_numberWithSpace,1 , length(v_numberWithSpace) -1);    
end;

I like to have the following output for v_numberwithSpace:

1234 5678 944

Now it leaves whitespace at the end of the string. I have put a . ad the code to make it more clear to what i mean. Whitout space at the begin and at the end. I have tried this, but it doesn't work for me.

Upvotes: 2

Views: 2141

Answers (2)

Aleksej
Aleksej

Reputation: 22949

If I understand your needs, you have to better handle the loop condition, looping not for all the lenght of the starting number, but considering it in group of 4 chars; besides, if you only need one string as a result, the PUT_LINE goes out of the loop:

declare    
  v_nr number DEFAULT 12345678944;    
  v_numberWithSpace varchar2(255);    
  v_count number DEFAULT 1;    
begin    
  while v_count <= length(v_nr) loop    
    v_numberWithSpace := v_numberWithSpace || substr(v_nr, v_count, 4)|| ' ';    
    v_count := v_count + 4;        
  end loop;
  DBMS_OUTPUT.PUT_LINE( trim(v_numberWithSpace));          
end;

This will give:

1234 5678 944

Upvotes: 4

Alex Poole
Alex Poole

Reputation: 191275

The regular expression solution from the Ask Tom post you linked to works with trivial modification:

select trim(regexp_replace(to_char(12345678944), '(....)', '\1 ')) as result
from dual;

RESULT                  
-------------------------
1234 5678 944

The trim() is there because a value that is an exact multiple of 4 digits would be given a trailing space by the regex; other values do not. The only other change is to make the pattern four characters rather than three, with an extra period inside the parentheses.

That works with integers of any size, as shown by a random sample:

with t (n) as (
  select trunc(dbms_random.value(power(10, level -1), power(10, level)))
  from dual
  connect by level <= 20
)
select n,
  trim(regexp_replace(to_char(n), '(....)', '\1 ')) as result,
  length(trim(regexp_replace(to_char(n), '(....)', '\1 '))) as result_length
from t;

                    N RESULT                    RESULT_LENGTH
--------------------- ------------------------- -------------
                    9 9                                     1
                   45 45                                    2
                  483 483                                   3
                 3178 3178                                  4
                73389 7338 9                                6
               192365 1923 65                               7
              3662474 3662 474                              8
             97356484 9735 6484                             9
            446571637 4465 7163 7                          11
           9557657854 9557 6578 54                         12
          25608546675 2560 8546 675                        13
         511047589116 5110 4758 9116                       14
        7429787110250 7429 7871 1025 0                     16
       13021942429553 1302 1942 4295 53                    17
      563394031257243 5633 9403 1257 243                   18
     6504958239800949 6504 9582 3980 0949                  19
    84778674020192456 8477 8674 0201 9245 6                21
   178163119511742275 1781 6311 9511 7422 75               22
  8473846546814521167 8473 8465 4681 4521 167              23
 95084346610621497023 9508 4346 6106 2149 7023             24

I've just shown the result length so you can see there are no trailing spaces.

Upvotes: 5

Related Questions