1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Problem with Parsing Name & Address??

Discussion in 'SQL PL/SQL' started by tiennt, Jan 25, 2012.

  1. tiennt

    tiennt Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    London
    I intend to write procedures/functions in PL/SQL to parse Name field into various fields : Title, FirstName, MiddleName, LastName, Gender
    E.g:
    Nguyen Van A
    Nguyen Thi B

    After parsing, the result will be shown as:

    Title FirstName MiddleName LastName Gender
    Mr Nguyen Van A Male
    Ms Nguyen Thi B Female
    Parsed.gif
    I supposed that Title & Gender are realized through MiddleName field. If MiddleName's values in (Thi, Dieu) then Title is assigned as Ms, and Gender = "F". Otherwise, Title = "Mr", and Gender = "M".


    2/ Another procedure/function is ParseAddress with the requirement as:
    Address field is divided into Street, Group, Area, Ward, County fields
    E.g.:
    No 6 Sum Street - Group 8 - Area 2 - ABCD Ward - London

    The result:

    Street Group Area Ward County
    No 6 Sum Street Group 8 Area 2 ABCD London

    I have tried coding by Visual Basic, it is OK. But if I interpret to PL/SQL -> it doesn't work. Please give me insructions. Thank you very much for your support!
    Kind regards!
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi,
    What is your requirement and what do you expect from us ??
     
  3. tiennt

    tiennt Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    London
    I expect that you can write functions to parse address and name under the formats of data above (of course by PL/SQL)
    Thank you very much indeed.
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Share your table(s) description here. we will definitely help you out.

    Why you are choosing PL/SQL while it can be done through SQL??
     
  5. tiennt

    tiennt Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    London
    Hi Kiran Marla,
    The Customer table here:
    Customer_id number,
    Customer_name varchar2(100),
    Customer_type number,
    Customer_Address varchar2(100),
    County_id number).

    Now I would like to parse customer_name into Title, FirstName, MiddleName, LastName, Gender. The Title field will be based on values of the MiddleName field to set values for it as I said above, so I could define which gender is for the Gender field.

    Moreover, the Customer_Address field should be parsed into separated fields as the sample of data I provided above.
    I want to use PL/SQL is because I want to insert the parsed data into new table. However, if you can do it with SQL that is very fine.
    Thank you very much for your enthusiasm!
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Also please provides us code you tried so far.
     
  7. tiennt

    tiennt Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    London
    Code (SQL):
         Sub ParseName(ByVal S AS String, Title AS String, FName AS String, _
                         MName AS String, LName AS String)
          Dim Word AS String, P AS INTEGER, Found AS INTEGER
          Const Titles = "Thi"
         ' Const Titles = "Thi.Mrs.Ms.Dr.Miss,Sir,Madam"
            Title = ""
            FName = ""
            MName = ""
            LName = ""
          '

          ' Get Title
          '

            Word = CutWord(S, S)
            IF InStr(Titles, Word) THEN
              Title = "Ms."
            ELSE
              Title = "Mr."
              ''S = Word & " " & S
            END IF
          '
          '
    GET the rest
          '
            LName = CutLastWord(S, S)   '
    LAST Name
            FName = CutWord(S, S)       ' First Name
            MName = Trim(S)             '
    Initials/Middle Name(s)
          END Sub
     
    --------------------------------------------
    ''-----Parse Name------------------------
    Function CutLastWord(ByVal S As String, Remainder As String) _
    As String
    ' CutLastWord: returns the last word in S.
    ' Remainder: returns the rest.
    '
    ' Words are separated by spaces
    '
    Dim I As Integer, P As Integer
    S = Trim$(S)
    P = 1
    For I = Len(S) To 1 Step -1
    If Mid$(S, I, 1) = " " Then
    P = I + 1
    Exit For
    End If
    Next I
    If P = 1 Then
    CutLastWord = S
    Remainder = ""
    Else
    CutLastWord = Mid$(S, P)
    Remainder = Trim$(Left$(S, P - 1))
    End If
    End Function

    ''----Create CutWord Function------------------

    Function CutWord(ByVal S As String, Remainder As String) As String
    '
    ' CutWord: returns the first word in S.
    ' Remainder: returns the rest.
    '
    Dim P As Integer
    S = Trim$(S)
    P = InStr(S, " ")
    If P = 0 Then P = Len(S) + 1
    CutWord = Left$(S, P - 1)
    Remainder = Trim$(Mid$(S, P + 1))
    End Function
    -------------------------

    For ParseAddress...-> it is not complete
    Many Thanks
     
  8. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Can be done with straight SQL.

    Code (SQL):

    SQL> -- parsing the name 'Nguyen Van Ann'
    SQL>
    SQL> WITH REC AS (SELECT 'Nguyen Van Ann' NAME
                FROM DUAL),
      2    3  REC1 AS ( SELECT SUBSTR(NAME, 1,INSTR(NAME||' ',' ',1)-1) FNAME,
      4                   SUBSTR(NAME, INSTR(NAME,' ',1)+1,
      5                          INSTR(NAME,' ',1,2)- INSTR(NAME,' ',1,1)-1) MNAME ,
      6                   SUBSTR(NAME, INSTR(NAME,' ',1,2)+1) LNAME
      7            FROM REC)
      8  SELECT CASE WHEN MNAME IN ('Thi', 'Dieu')
      9              THEN 'Ms'
     10              ELSE 'Mr'
     11          END TITLE,
     12          FNAME,
     13          MNAME ,
     14          LNAME,
     15          CASE WHEN MNAME IN ('Thi', 'Dieu')
     16              THEN 'F'
     17              ELSE 'M'
     18          END GENDER
     19  FROM rec1;

    TITLE      FNAME  MNAME      LNAME      GENDER
    ---------- ------ ---------- ---------- ----------
    Mr         Nguyen Van        Ann        M

    SQL>
     
     
    tiennt likes this.
  9. tiennt

    tiennt Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    London
    Oh my God, you do it so quick & easy to understand.. Thank you so so much.
    If possible, could you parse the customer_address, please?
    Many thanks
     
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Its your turn now:)
    Try with same logic. You need to check for '-' instead of ' '. Thats it.
    Also post the result once its done.
     
    tiennt likes this.
  11. tiennt

    tiennt Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    London
    I will try! If I get trouble with it please give me some advice, expert :)
     
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    We will be happy to guide you further once we see your work.