CODEWITHSUNDEEP
sqlstringoracle-databasesplitappend
Johns smith88
Johns smith88

Reputation: 19

Oracle SQL splitting a string field on character count and append a letter at the end

I am facing a situation where I have a Oracle database table String field where I want to append a letters at beggining and at end with size max length 5.

eg- The  original string = AAAAABBBBBCCCCCDDDDD
out put string =EAAAAAFEBBBBDFECCCCCFEDDDDDF

Here after character size of 5 is identified E is appended at the beginning and F at the end. how can I achieve this using oracle sql select query?

Thanks

Upvotes: 0

Views: 296

Answers (1)

Aleksej
Aleksej

Reputation: 22949

Assuming that your output string has a typo and that it should be 'EAAAAAFEBBBBBFECCCCCFEDDDDDF', this could be a way:

SELECT sys_connect_by_path( substr(x, 1 + (level-1)*5, 5) || 'F', 'E')
  FROM (select 'AAAAABBBBBCCCCCDDDDD' x from dual) text
where connect_by_isleaf = 1  
CONNECT BY level * 5 <= length(x)

How it works: the CONNECT BY impements recursion and the condition level * 5 <= length(x) makes it stop when level (the number of iteration) is big enough to comsume all the string. With this string, you have 4 groups of 5 chcracters each, so you get 4 iterations:

SQL> select level
  2    FROM (select 'AAAAABBBBBCCCCCDDDDD' x from dual) text
  3  CONNECT BY level * 5 <= length(x);

     LEVEL
----------
         1
         2
         3
         4

Once you have recursion, at each iteration you need to get the nth group of characters:

SQL> select level, substr(x, 1 + (level-1)*5, 5)
  2    FROM (select 'AAAAABBBBBCCCCCDDDDD' x from dual) text
  3  CONNECT BY level * 5 <= length(x);

     LEVEL SUBSTR(X,1+(LEVEL-1)*5,5)
---------- --------------------------------
         1 AAAAA
         2 BBBBB
         3 CCCCC
         4 DDDDD

Now you need a way to concatenate all these substrings, adding 'E' and 'F'; here I use sys_connect_by_path by concatenating the 'F' at the end and using the 'E' as separator:

SQL> select level, sys_connect_by_path( substr(x, 1 + (level-1)*5, 5) || 'F', 'E') result
  2    FROM (select 'AAAAABBBBBCCCCCDDDDD' x from dual) text
  3  CONNECT BY level * 5 <= length(x);

     LEVEL RESULT
---------- ------------------------------
         1 EAAAAAF
         2 EAAAAAFEBBBBBF
         3 EAAAAAFEBBBBBFECCCCCF
         4 EAAAAAFEBBBBBFECCCCCFEDDDDDF

Last step, you just need the "last" row, that is the leaf of the tree generated by recursion, so you add

where connect_by_isleaf = 1

To handle the case in which the latest group of characters is less than 5, you can edit the CONNECT BY clause into:

CONNECT BY (level-1) * 5 < length(x);

Upvotes: 1

Related Questions