| 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?
|
|
|
|
|
|
|
|
|
|
| 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: 3273
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.
|
|
|
|
| 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.
|
|
|
|
| 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.
SELECT
SUBSTR(123456791,1,CEIL(LENGTH(123456791)/2)) || ',' || SUBSTR(123456791,CEIL(LENGTH(123456791)/2+1),LENGTH(123456791)) AS SPLIT
FROM DUAL
|
|
|
|
 Re: split string in two halves [message #599339 is a reply to message #599336] |
Wed, 23 October 2013 14:39 |
 |
Michel Cadot
Messages: 68647
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 |
Solomon Yakobson
Messages: 3273
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.
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
