OraFAQ Forum: SQL & PL/SQL » split string in two halves

Home » SQL & PL/SQL » SQL & PL/SQL » split string in two halves (Oracle 11g, Win7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

split string in two halves [message #599260]

Wed, 23 October 2013 06:28

nischalinn
Messages: 118
Registered: May 2012
Location: nepal

Senior Member

I want to split any user given string into two parts, how can I do it.

suppose some sample inputs may be:

'123456' splits into '123','456' ,'123456789' splits into '12345','6789'

NOTE: the first half splitted string length is greater in case of odd no. string as in second example

How can I do it?

Report message to a moderator

Re: split string in two halves [message #599262 is a reply to message #599260]

Wed, 23 October 2013 06:36

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

LENGTH + CEIL + concat of two SUBSTR. Or LENGTH + CEIL + REGEXP_REPLACE.

SY.

[Updated on: Wed, 23 October 2013 06:37]

Report message to a moderator

Re: split string in two halves [message #599266 is a reply to message #599262]

Wed, 23 October 2013 07:08

nischalinn
Messages: 118
Registered: May 2012
Location: nepal

Senior Member

Thanks for your quick reply.

Will you please provide me how to do it.

Report message to a moderator

Re: split string in two halves [message #599268 is a reply to message #599266]

Wed, 23 October 2013 07:24

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Post what you have so far.

SY.

Report message to a moderator

Re: split string in two halves [message #599269 is a reply to message #599266]

Wed, 23 October 2013 07:26

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Whilst Solomon may well be magnanimous enough to do your (home?)work for you, don't you think that you should try it yourself just to see if you can do it? Give it a try, there's no shame in getting it wrong. If it doesn't work out, post your attempt(s) here. People will be happy to help someone willing to put in a bit of effort. Conversely, people tend to get a bit annoyed at those who just expect others to do their work for them.

ETA
note this thread The OP posted his attempt that was enough to show that he was willing to put in the effort. He promptly got a very tidy solution from (once again) SY

[Updated on: Wed, 23 October 2013 07:28]

Report message to a moderator

Re: split string in two halves [message #599271 is a reply to message #599269]

Wed, 23 October 2013 07:43

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Well, I wouldn't compare the two posts. Regular expressions are not that straight-forward while splitting a string in half requires no more than middle (if not elementary) school education (especially when OP is given all the clues).

SY.

Report message to a moderator

Re: split string in two halves [message #599272 is a reply to message #599271]

Wed, 23 October 2013 07:47

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

My point was about putting in the effort and showing willing to make an attempt. The pointer to the other thread was to highlight that such effort would not be wasted, and that further help would be forthcoming, not that simply throwing out some vague attempt would result in someone immediately writing the solution for the OP.

Report message to a moderator

Re: split string in two halves [message #599336 is a reply to message #599272]

Wed, 23 October 2013 14:07

akull
Messages: 46
Registered: July 2012
Location: Argentina

Member

Hi Guys!

I put my effort to try at least get an approach to what nischalinn is requiring. I must tell you the truth, I've worked with Oracle for 4 years, but now I'm programming in another language, so my skills in Oracle are getting a bit rusty, even through I was certified as OCA a long time ago.

I don't know whether my example is correct or not, but at least I wanted to help. Hope anyone get mad at me.

Code: [Select all] [Show/ hide]

SELECT 
SUBSTR(123456791,1,CEIL(LENGTH(123456791)/2)) || ',' || SUBSTR(123456791,CEIL(LENGTH(123456791)/2+1),LENGTH(123456791)) AS SPLIT
FROM DUAL

Report message to a moderator

Re: split string in two halves [message #599339 is a reply to message #599336]

Wed, 23 October 2013 14:39

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

@akull, good work!
You don't need to specify the length if you want the substring till the end of the string:

Code: [Select all] [Show/ hide]

SQL> with 
  2    data as (
  3      select '123456' val from dual
  4      union all 
  5      select '123456789' from dual
  6    )
  7  select val, 
  8         SUBSTR(val,1,CEIL(LENGTH(val)/2)) || ',' || SUBSTR(val,CEIL(LENGTH(val)/2+1)) as split
  9  from data
 10  /
VAL       SPLIT
--------- -------------------
123456    123,456
123456789 12345,6789

[Updated on: Thu, 06 March 2014 13:03]

Report message to a moderator

Re: split string in two halves [message #599341 is a reply to message #599336]

Wed, 23 October 2013 14:44

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

You did well. One comment though - if substr third parameter is omitted it defaults to the end of the string.

Code: [Select all] [Show/ hide]

SELECT  SUBSTR(123456791,1,CEIL(LENGTH(123456791)/2)) || ',' || SUBSTR(123456791,CEIL(LENGTH(123456791)/2+1)) AS SPLIT
  FROM  DUAL
/

SPLIT
----------
12345,6791

SCOTT@orcl >

But I think I misunderstood OP and said concat of two SUBSTR. While re-reading the post I realized OP asked to split it into two parts:

Code: [Select all] [Show/ hide]

WITH T AS (
           SELECT '123456' STR FROM DUAL UNION ALL
           SELECT '123456789' FROM DUAL UNION ALL
           SELECT '1' FROM DUAL
          )
SELECT  STR,
        SUBSTR(STR,1,CEIL(LENGTH(STR)/2)) PART1,
        SUBSTR(STR,CEIL(LENGTH(STR)/2) + 1) PART2
  FROM  T
/

STR       PART1     PART2
--------- --------- -----
123456    123       456
123456789 12345     6789
1         1

SCOTT@orcl >

And with REGEXP_REPLACE:

Code: [Select all] [Show/ hide]

WITH T AS (
           SELECT '123456' STR FROM DUAL UNION ALL
           SELECT '123456789' FROM DUAL UNION ALL
           SELECT '1' FROM DUAL
          )
SELECT  STR,
        REGEXP_REPLACE(STR,'.{' || TRUNC(LENGTH(STR)/2) || '}$') PART1,
        REGEXP_REPLACE(STR,'^.{' || CEIL(LENGTH(STR)/2) || '}') PART2
  FROM  T
/

STR       PART1     PART2
--------- --------- -----
123456    123       456
123456789 12345     6789
1         1

SCOTT@orcl >

Or with REGEXP_SUBSTR:

Code: [Select all] [Show/ hide]

WITH T AS (
           SELECT '123456' STR FROM DUAL UNION ALL
           SELECT '123456789' FROM DUAL UNION ALL
           SELECT '1' FROM DUAL
          )
SELECT  STR,
        REGEXP_SUBSTR(STR,'^.{' || CEIL(LENGTH(STR)/2) || '}') PART1,
        REGEXP_SUBSTR(STR,'.{' || TRUNC(LENGTH(STR)/2) || '}$') PART2
  FROM  T
/

STR       PART1     PART2
--------- --------- -----
123456    123       456
123456789 12345     6789
1         1

SCOTT@orcl >

SY.

Report message to a moderator