Professional Web Applications Themes

Remove variable amount of characters from a string - MySQL

Hello all. I have a field in my table which contains a string that is made of of a series of numbers, followed by an 'arrow' (->), followed by some more numbers. It will look roughly like this : 123->4567 Both the series of numbers before and after the arrow are variable between 1 and 9 numbers. I'm looking for a way to extract the numbers to be able to use them in a view (so it can either be a statement or a function). Looking around, I saw mention of functions like 'LEFT' and 'RIGHT' or 'SUBSTR', but I ...

  1. #1

    Default Remove variable amount of characters from a string

    Hello all.

    I have a field in my table which contains a string that is made of of
    a series of numbers, followed by an 'arrow' (->), followed by some
    more numbers. It will look roughly like this :
    123->4567
    Both the series of numbers before and after the arrow are variable
    between 1 and 9 numbers.
    I'm looking for a way to extract the numbers to be able to use them in
    a view (so it can either be a statement or a function).

    Looking around, I saw mention of functions like 'LEFT' and 'RIGHT' or
    'SUBSTR', but I only found mention of a being able to remove a fixed
    amount of characters.

    Does anyone have an idea of how to remove a variable amoung of
    characters from the string ?

    Many thanks for any assistance.

    Anton

    Mad Guest

  2. #2

    Default Re: Remove variable amount of characters from a string

    Mad Ant wrote: 

    As you have the '->', then you can always use a function to locate where it's
    placed and that way you can know how long the number on the left side is

    http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_position

    As you now easily can determine the length of the string you want, you can use
    SUBSTR() without any trouble.


    If you want to make it even simpler, you can use

    http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index

    --

    //Aho
    J.O. Guest

  3. #3

    Default Re: Remove variable amount of characters from a string

    On Jun 22, 11:43 am, "J.O. Aho" <net> wrote: 

    >
    > As you have the '->', then you can always use a function to locate where it's
    > placed and that way you can know how long the number on the left side is
    >
    > http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function...
    >
    > As you now easily can determine the length of the string you want, you can use
    > SUBSTR() without any trouble.
    >
    > If you want to make it even simpler, you can use
    >
    > http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function...
    >
    > --
    >
    > //Aho[/ref]

    The SUBSTRING_INDEX function is perfect. Thanks loads.

    Anton

    Mad Guest

Similar Threads

  1. FIND Amount in String
    By Vbprog40 in forum Macromedia ColdFusion
    Replies: 5
    Last Post: June 13th, 10:38 PM
  2. Special Characters in Component's String-type variable
    By drailer webforumsuser@macromedia.com in forum Macromedia Flash Actionscript
    Replies: 4
    Last Post: January 14th, 04:09 PM
  3. remove ^B characters
    By Reiner Griess in forum PHP Development
    Replies: 0
    Last Post: October 22nd, 09:49 AM
  4. How do I count blocks of characters in a string variable?
    By Researcher in forum PERL Miscellaneous
    Replies: 5
    Last Post: September 8th, 12:53 PM
  5. Textarea - Limit Amount of Characters?
    By Richard Hockey in forum PHP Development
    Replies: 2
    Last Post: August 23rd, 04:34 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139