Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Apr 2012
    Posts
    15

    Unanswered: PL/SQL Split string into two column

    Hi Guys

    I wonder if any one can help me, I a bit of a noob. I need to split to a string from one table and split into two columns in another table. There is a few different variety of numbers and rules but no clear delimiter. I was how this can be done. Do I use combo of SUBSTR and INSTR or use if then loops to satisfy all the rules.
    Thanks in advance.

    Table

    5 Kent Street
    3 A lindt Street
    2/15 bold Street
    9/34-36 carmen Road
    12/5a sandford Street

    Result

    Number |Street
    ----------------
    5 |Kent Street
    3A |lindt Street
    2/15 |bold Street
    9/34-36 |carmen Road
    12/5a |sandford Street

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    https://forums.oracle.com/forums/thr...75443&tstart=0

    >Do I use combo of SUBSTR and INSTR or
    SQL only
    >use if then loops to satisfy all the rules.
    PL/SQL

    choice is yours
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Based on sample data you provided, here's one option.
    Code:
    SQL> select
      2    col,
      3    regexp_substr(col, '\d+(\s|\/)(\d+)?-?(\d+)?(\w {1})?') res1,
      4    regexp_substr(col, '(\w+\s\w+)$') res2
      5  from test;
    
    COL                            RES1       RES2
    ------------------------------ ---------- ---------------
    5 kent street                  5          kent street
    3 a lindt street               3 a        lindt street
    2/15 bold street               2/15       bold street
    9/34-36 carmen road            9/34-36    carmen road
    12/5a sandford street          12/5a      sandford street
    
    SQL>

  4. #4
    Join Date
    Apr 2012
    Posts
    15
    Hi

    Thanks for the response, there seems to be more variety where if word in front of number also get split into second column and if Cnr is found then it stop and split the rest into second columns.

    COL RES1 RES2
    ------------------------------ ---------- ---------------
    5 kent street 5 kent street
    3 a lindt street 3 a lindt street
    2/15 bold street 2/15 bold street
    9/34-36 carmen road 9/34-36 carmen road
    12/5a sandford street 12/5a sandford street
    shop 231 John St shop 231 John St
    Cnr 321 Pollock RD 321 Pollock RD
    368 Cnr Barker & Anzac Barker & Anzac Pde

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think that it might be better to list up all the considerable cases, before making SQL codes.

    For example, are these cases possible?
    Code:
    COL                            RES1       RES2
    ------------------------------ ---------- --------------------
    7/1 An oldwoman's street       7/1        An oldwoman's street
    7/1 A youngman's street        7/1        A youngman's street
    13-5 ne main street            13-5 ne    main street
    13-5 sw main street            13-5 sw    main street

  6. #6
    Join Date
    Apr 2012
    Posts
    15
    Thanks for the reponse. I've written down some logic please let me know if this will work.

    If

    crn is found in the String

    Then

    Everything after crn is added into column 2, column 1is null and end

    Else if

    PO BOX is found in the string and end

    Then

    Everything is added into the column 2. column 1is null

    Else if

    No numbers are found in the string and end

    Then

    Everything added into column 2. column 1 is null and end

    Else

    Find last number.
    (Last number and everything before put into column1)
    (Everything after the last number gets put into column2)

    end

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ...

    Else

    Find last number.
    (Last number and everything before put into column1)
    (Everything after the last number gets put into column2)

    end
    I thought this contradicts some samples in the first post.


    Table

    5 Kent Street
    3 A lindt Street
    2/15 bold Street
    9/34-36 carmen Road
    12/5a sandford Street
    Result

    Number |Street
    ----------------
    5 |Kent Street
    3A |lindt Street
    2/15 |bold Street
    9/34-36 |carmen Road
    12/5a |sandford Street
    Last edited by tonkuma; 04-15-12 at 03:31. Reason: Add last example for doutful case.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1)
    ... and if Cnr is found then it stop ...
    If

    crn is found in the String
    'Cnr' or 'crn'?


    (2) How to know "PO BOX"?
    Is it mean a sring of 'PO BOX'?
    Multiple blanks beween 'PO' and 'BOX' or 'POBOX' should treat same as 'PO BOX' or not?


    (3) Another question is "distinguish upper case and lower case" or not?
    'Cnr' and 'cnr'
    'PO BOX', 'PO Box' and 'Po Box'
    Last edited by tonkuma; 04-15-12 at 03:38. Reason: Exchange (2) and (3)

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (Everything after the last number gets put into column2)
    It might be better to add a phrase, like...
    (Everything after the last number gets put into column2 by removing leading blanks)


    Else if

    PO BOX is found in the string and end

    Then

    Everything is added into the column 2. column 1is null
    With the string 'PO BOX'? or removing 'PO BOX'?

  10. #10
    Join Date
    Apr 2012
    Posts
    15
    Quote Originally Posted by tonkuma View Post
    (1)



    'Cnr' or 'crn'?


    (2) How to know "PO BOX"?
    Is it mean a sring of 'PO BOX'?
    Multiple blanks beween 'PO' and 'BOX' or 'POBOX' should treat same as 'PO BOX' or not?


    (3) Another question is "distinguish upper case and lower case" or not?
    'Cnr' and 'cnr'
    'PO BOX', 'PO Box' and 'Po Box'

    2)String contain "PO BOX" with one space in between.

    3) no distinguish between upper and lower case,

  11. #11
    Join Date
    Apr 2012
    Posts
    15
    Quote Originally Posted by tonkuma View Post
    It might be better to add a phrase, like...
    (Everything after the last number gets put into column2 by removing leading blanks)



    With the string 'PO BOX'? or removing 'PO BOX'?
    Don't remove PO BOX

    If the string contain "PO BOX" (upper or lower case) the full string is moved to column 2.

    e.g String: PO BOX 2342

    Column 2 result: PO BOX 2342
    Column 1 result: Null

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    ...

    Else

    Find last number.
    (Last number and everything before put into column1)
    (Everything after the last number gets put into column2)

    end
    I thought this contradicts some samples in the first post.

    Table

    5 Kent Street
    3 A lindt Street
    2/15 bold Street
    9/34-36 carmen Road
    12/5a sandford Street
    Result

    Number |Street
    ----------------
    5 |Kent Street
    3A |lindt Street
    2/15 |bold Street
    9/34-36 |carmen Road
    12/5a |sandford Street
    Why did you add 'A' and 'a' to first part(Number)?
    I guessed 'a' was affixed to the last number.
    But, 'A' was separated from the last number by a blank...
    And, a blank between '3' and 'A' was removed in first part(Number)...

    Please give me a rational explanation.


    Please also answer to this question.
    Note: If compare "3 A lindt Street" and "7/1 A youngman's street", I thought rational explanation might be neccesary.
    Quote Originally Posted by tonkuma View Post
    I think that it might be better to list up all the considerable cases, before making SQL codes.

    For example, are these cases possible?
    Code:
    COL                            RES1       RES2
    ------------------------------ ---------- --------------------
    7/1 An oldwoman's street       7/1        An oldwoman's street
    7/1 A youngman's street        7/1        A youngman's street
    13-5 ne main street            13-5 ne    main street
    13-5 sw main street            13-5 sw    main street
    If those cases never appear, simply answer 'NO'.


    And please answer this.
    Quote Originally Posted by tonkuma View Post
    (1)

    'Cnr' or 'crn'?
    Last edited by tonkuma; 04-15-12 at 16:53. Reason: Add Note.

  13. #13
    Join Date
    Apr 2012
    Posts
    15
    Quote Originally Posted by tonkuma View Post
    Why did you add 'A' and 'a' to first part(Number)?
    I guessed 'a' was affixed to the last number.
    But, 'A' was separated from the last number by a blank...
    And, a blank between '3' and 'A' was removed in first part(Number)...

    Please give me a rational explanation.


    Please also answer to this question.
    Note: If compare "3 A lindt Street" and "7/1 A youngman's street", I thought rational explanation might be neccesary.

    If those cases never appear, simply answer 'NO'.


    And please answer this.
    The A is also affixed to the last number so when but the result it will remove the space in between it.

    String: 7/1 A youngman's street

    Result
    Name|street
    ------------
    7/1A|youngman's street



    Is looking for Cnr

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The A is also affixed to the last number so when but the result it will remove the space in between it.
    But, there is a blank between last number and 'A'.
    Your sample '3 A lindt Street' was same.

    How to know it is affix?
    For example,
    if there was 19-7 of 'Z foundation avenue' and expressed '19-7 Z foundation avenue' in table,
    how to know it is 19-7 of 'Z foundation avenue' and not 19-7Z of 'foundation avenue'?

    Or, those names starting with one character never was?


    By the way,
    I found 'Z foundation' on the net Z Foundation | Denver, CO 80238
    Last edited by tonkuma; 04-16-12 at 01:44. Reason: Add "By the way, ..." Replace association to foundation.

  15. #15
    Join Date
    Apr 2012
    Posts
    15
    Hi

    I clarified this to find affixed is just a letter followed by a number. There will not be a case where e.g 19-7 of 'Z foundation avenue' only case where 19-7Z of 'foundation avenue'

    Sorry about the confusion.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •