Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Saurabh.

Asked: January 10, 2018 - 12:07 pm UTC

Last updated: January 17, 2018 - 9:50 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi ,
I have a row that may contains upto 6000 char length string in a column. I want to check the length of string and then split the string into smaller string each of length 2000 and insert that into a second table. I want to do that in sql only. Please help on this.

For eg if a row contain 4000 length string, i will get only 2 smaller string of 2000 char and insert 2 rows in the second table.

Thanks
Saurabh

and Chris said...

Here's one way to do it:

- Cross join your rows with a dummy table that has (at least) as many rows as you want to split your string into. e.g. from your example 4000/2000 = 2
- Substr the string, starting at the ((N - 1) * M ) + 1 point where
- N is the current row of the dummy table
- M is how many characters you want in each output row

Put it all together and you get something like:

with rws as (
  select level x from dual
  connect by level <= 10
), strs as (
  select lpad('1234568790', 100, '1234568790') str from dual
)
  select x, substr(str, 1 + ((x-1)*10), 10) sub
  from   rws cross join strs;

X    SUB          
   1 1234568790   
   2 1234568790   
   3 1234568790   
   4 1234568790   
   5 1234568790   
   6 1234568790   
   7 1234568790   
   8 1234568790   
   9 1234568790   
  10 1234568790

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

You deserve a Nobel

Saurabh Kumar, January 17, 2018 - 6:36 pm UTC

You must be getting thousands of question maybe repetitive, but you answer them like its your first question. You genuinely deserve an nobel prize.
Chris Saxon
January 17, 2018 - 9:50 pm UTC

Thanks, I don't think the Nobel committee has an "answering questions on internet forums" category though ;)

vishnudas s.a, February 28, 2018 - 4:38 am UTC

answer is kind of difficult for me..it might be i am not enough a
'SQL GUY'.. anyway thank you for your response :)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.