• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

Extracting values connected with hyphens

Hi Experts,

I am trying to figure out a way of extracting integer values on both sides of a hyphen (ex: 7-14) from the string in recordset field, as I need to use the values in for - to loops, but so far without success.

To be more specific the aim is to generate a number of record copies corresponding to the two values - ex: like 10 instances of a record where the field contains ex: 6-15 or 4 instances if 17-20 are found. As the field sometimes contains more than one set of values separated by semicolon (ex: 6-15; 17-20) it should also be possible to loop through both (or more) so i need to know how many pairs of number-hyphen-number are within the string.

For clarification: the numbers I try to get at are weeknumbers written as text, and  I need to dig out every one of the weeks just entered as hyphen pairs in the string.

I get at the data by connecting to an MsExcel file.
Heres the code:

<%option explicit%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<title>Scrape XLS</title>


Dim OpenFile,i  
Dim Conn,objRec,strSQL  
Dim locname

OpenFile = "F14_1584.xls"  
Set Conn = Server.CreateObject("ADODB.Connection")  
'*** Connect to Excel ***'  
Conn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_  
"Excel 8.0; DBQ=" & Server.MapPath(OpenFile) & "; " 
'*** Select Sheet ***'  
strSQL = "SELECT * FROM [Sheet$]"  
Set objRec = Conn.Execute(strSQL)  
<table border="0">  
<tr align="left">  
          <th scope="col" width="120px">Lokale</th>
          <th scope="col" width="80px">Dag</th>
          <th scope="col" width="50px">fra</th>
          <th scope="col" width="50px">til</th>
          <th scope="col" width="50px">var.</th>
          <th scope="col" width="90px">res. u.</th>
          <th scope="col" width="250px">Name</th>
          <th scope="col" width="550px">Description</th>
Do While Not objRec.EOF

locname = objRec("Allocated location name").Value

dim begh
dim endh
dim scw

begh = left(objRec.Fields("Scheduled Start Time").Value, 5)
endh = left(objRec.Fields("Scheduled End Time").Value, 5)
scw = objRec.Fields("Scheduled Weeks").Value

        <tr valign="top">  
        <td><%=objRec.Fields("Scheduled Days").Value%></td>  

        <!-- this is the field in question -->


Set objRec=Nothing  
Set Conn=Nothing  

Does someone have a tip or two?

Best regards
  • 2
2 Solutions
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
i'm not sure what your exact output is supposed to look like but if I understand you correctly, you're looking for a way extract a substring "3-7" out of a longer string and break out those individuals numbers. hopefully I understand that right, it's still early and I haven't had my morning caffeine :)

dim strOfNumRanges: strOfNumRanges = "1-4;2-9;13-22"

'-- put each range into an array element
arrOfNumRanges = Split( strOfNumRanges, ";")

'-- now loop through to isolate each number range
for each numberRange in arrNumRanges
    if numberRange <> "" then
        arrNumbers = Split( numberRange, "-" )

       '-- get each number
       num1 = arrNumbers( 0 )
       num2 = arrNumbers( 1 )

       '-- do whatever, write it out to the screen or you can loop through using those values
       Response.Write "<td>" & num1 & "</td><td>" & num2 & "</td>"

    end if

Open in new window

If you are using VBScript then I would use the Split-function against the semi-colon, to get an array of ranges.  Loop through that array, using Split-function against the dash-symbol to get from/to values.

If you are using SQL then I would dynamically build a where-clause using Replace-function:
1.      prefix your string with AND fieldname between
2.      replace semicolon with AND fieldname between
3.      replace dash with AND
declare	@ranges	varchar(100)
,	@where	varchar(100)

set	@ranges	= '6-15; 17-20'

set	@where	= 'fieldname between ' + replace(replace(@ranges,';' and fieldname between'),'-',' and ')

Open in new window

MortenWBAuthor Commented:
Thank you both for responding - I will test your solutions, but have to leave office now - I'll return later.

Best regards
MortenWBAuthor Commented:
Hi Big Monty and John,

yes, you both pointed me in the right direction, and I am now in progress again:)

Big Monty - your solution fits into my VBscript experience, so thats what I'm using, and John_Vidmar - I use some aspects of Your idea of a WHERE clause into my SQL - although I'm unfamiliar with the script language You use.

Thank you both for your help
Best regards
Morten B
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now