Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    17

    Lightbulb Unanswered: Parsing a string for multiple values

    I need to parse the following string:

    "( B Panel 48.1248 X 93.5 CRS Gage 16)"

    I need:
    1. The letter preceding the word "Panel" in this case "B"
    2. The number following the word "Panel" in this case "48.1248"
    3. The number following the word "Gage" in this case "16"

    Everything else "should" stay constant, i.e. the format. Although the actual values will change and the length of them.

    I know that I can use InString, but the code is driving me crazy!

    Can anyone help me?
    Thanks for your help, Mitch

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, the code is really messy

    create table yourtable
    (str text)

    insert into yourtable (str)
    values ('( B Panel 48.1248 X 93.5 CRS Gage 16)')

    SELECT str
    , mid(str, instr(str,'Panel')-2, 1) as LetterPrecedingPanel
    , len(str) as LengthOfstr
    , len(str)-instr(str,'Panel')-5 as LengOfRest
    , mid(str, instr(str,'Panel')+6
    , len(str)-instr(str,'Panel')-5) as RestOfstr
    , instr( mid(str, instr(str,'Panel')+6
    , len(str)-instr(str,'Panel')-5), ' ') as NextSpace
    , left( mid(str, instr(str,'Panel')+6
    , len(str)-instr(str,'Panel')-5)
    , instr( mid(str, instr(str,'Panel')+6
    , len(str)-instr(str,'Panel')-5), ' ') ) as TheNumber
    FROM yourtable;


    rudy
    http://r937.com/

  3. #3
    Join Date
    Mar 2003
    Posts
    17

    Smile Thanks

    Wow, you are right that is ugly!

    Thanks a lot!
    Thanks for your help, Mitch

Posting Permissions

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