Home » SQL & PL/SQL » SQL & PL/SQL » split string in two halves (Oracle 11g, Win7)
split string in two halves [message #599260] Wed, 23 October 2013 06:28 Go to next message
nischalinn
Messages: 117
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?
Re: split string in two halves [message #599262 is a reply to message #599260] Wed, 23 October 2013 06:36 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1993
Registered: January 2010
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 Go to previous messageGo to next message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
Thanks for your quick reply.

Will you please provide me how to do it.
Re: split string in two halves [message #599268 is a reply to message #599266] Wed, 23 October 2013 07:24 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1993
Registered: January 2010
Senior Member
Post what you have so far.

SY.
Re: split string in two halves [message #599269 is a reply to message #599266] Wed, 23 October 2013 07:26 Go to previous messageGo to next message
pablolee
Messages: 2612
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1993
Registered: January 2010
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.
Re: split string in two halves [message #599272 is a reply to message #599271] Wed, 23 October 2013 07:47 Go to previous messageGo to next message
pablolee
Messages: 2612
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.
Re: split string in two halves [message #599336 is a reply to message #599272] Wed, 23 October 2013 14:07 Go to previous messageGo to next message
akull
Messages: 39
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.


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

icon8.gif  Re: split string in two halves [message #599339 is a reply to message #599336] Wed, 23 October 2013 14:39 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
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:
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1993
Registered: January 2010
Senior Member
You did well. One comment though - if substr third parameter is omitted it defaults to the end of the string.

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:

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:

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:

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.
Re: split string in two halves [message #599346 is a reply to message #599341] Wed, 23 October 2013 15:13 Go to previous messageGo to next message
akull
Messages: 39
Registered: July 2012
Location: Argentina
Member
Thanks Michael, SY

That's why I said my skills are getting rusty!

Thanks again for remainding me that!

Regards,
Steve!
Re: split string in two halves [message #601768 is a reply to message #599346] Mon, 25 November 2013 06:43 Go to previous message
nischalinn
Messages: 117
Registered: May 2012
Location: nepal
Senior Member
Thanks for the comments to every one participants.

Thank you very much.
Previous Topic: get data between rows
Next Topic: get the value of FOR loop outside the loop
Goto Forum:
  


Current Time: Wed Aug 20 11:56:47 CDT 2014

Total time taken to generate the page: 0.25249 seconds