Retrieving just the numbers from strings

Results 1 to 2 of 2

Thread: Retrieving just the numbers from strings

  1. #1
    Join Date
    Dec 1969

    Default Retrieving just the numbers from strings

    Hi, is anyone able to help me with this? I have been struggling with this problem for sometime.<BR><BR>I have been trying to code a conditional loop, to detact words and numbers. I have 3 types, numbers, numbers and words, just words. All these are stored in access database.<BR><BR>Can anyone help me detact the numbers, and retrieve the numbers from the words such as 15 mths so i can do calculation with the number 15?<BR><BR>Would anyone able to help me? Been to several forums but no responses so far...<BR>

  2. #2
    Join Date
    Dec 1969

    Default Yeah, not hard using regular expression

    &nbsp;<BR>You can&#039;t use regular expressions in SQL though, so I hope you don&#039;t mean you need to do this as part of the SQL query. I hope you mean you get the value from the recordset and *THEN* want to extract just the number.<BR><BR>Assuming that is so:<BR><BR>&#060;%<BR>...<BR>... make a SQL query and get a recordset ...<BR>... ending with something like: ...<BR><BR>Set RS = yourConnection.Execute( SQL )<BR><BR>&#039; first, set up the regular expression:<BR>Set numbersOnly = New RegExp<BR>numbersOnly.Pattern = "[^0-9]" &#039; all characters exceptdigits<BR>numbersOnly.Global = True &#039; affect all text tested<BR><BR>&#039; loop through all records<BR>Do Until RS.EOF<BR> &#039; say the the field in question is named "maybeNumber":<BR> text = RS("maybeNumber")<BR> maybe = numbersOnly.Replace( text, "" ) &#039; replace non-digits with nothing<BR> &#039; then see if any digits are left...<BR> If Len(maybe) &#062; 0 Then <BR> num = CLng( maybe ) &#039; get the digits *AS* a number!<BR> ... now manipulate that as you need to ...<BR> Else<BR> ... no digits in the RS field if you get here ...<BR> ... words only, so do as is appropriate ...<BR> End If<BR> ...<BR> RS.MoveNext &#039; next record<BR>Loop<BR>%&#062;<BR><BR>************<BR>< BR>If you need to handle negative numbers and/or numbers with decimal points in them, it&#039;s a bit more complex, but not too hard. Regular expressions are the key to it.<BR><BR>

Posting Permissions

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