Hi i have a quick question i have a database and i pull the data from it and one of the data im pulling is the telephone number people are entering but unfortunatly the coder that was here before me did not code the form correctly and by this people are able to enter all kind of telephone formats ex:

(123)123-4567, (123)-123-4567, (123)1234567. What i am trying to do is having the output be all the same format when they do a query, i want it to be this format:

123-123-4567

so what way can i achive this..
this is part of my code for the telephone query:


Response.Write " "&rstSearch2("unittelephone")


this shows all the telephone numbers in the database regardless of the format but I wish to force a format like the one i specified above.

Thanks.

I would:
1.. Write a script to extract, tweak then re-insert these existing values
2.. Chuck some validation in place for future inserts to ensure a standard format
3.. Check the rest of the previous developers work for other poor code

Anyhow try:
dim someVar,tweakedVar
someVar = "(123)123-4567"
tweakedVar = replace(mid(someVar,2,(len(someVar))),")","-")
response.write tweakedVar

Wind is your friend
Matt

mmmm I just realized you have threre different types of date formats, the code in my above post will only work for one of them being : "(123)123-4567"

For other formats I will explain what I have done above. With an understanding of how that works I'm sure you can change the code to work for your two other formats:

1..Google will explain better but here is a description of the VBS functions I have used:
   a.LEN (executes first, gets the length of your string)

   b.MID (executes second, returns a specified number of characters from a string. It taked two parameters, char position at where the part to be taken begins (2 - removes the very first char being an open bracket) and the number of characters to take - this number being the result of the LEN function)

   c.REPLACE (executes third, replaces one specified character with another from a string - I have specified the ")" is to be replaced with "-")


Wind is your friend
Matt

Alternatively you can just return all numbers to a 10 digtit number then space them out.
Its a little more verbose than matts but they basically are using the same principles.


======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

excuse me if i look "stupid" but were will i put that function? in my code, this is the code that shows up all the phone numbers.

please help im kind of new and still in the learning stage.

Thanks..

is there a easy way just to remove special characters from the output? like using not isnumeric() or probably something easier?

could someone please give me a example on how to do this.

(123)123-1234 i just want it to be reformated to 123-123-1234

Thanks..

netcrawler

Here is a cut n paste 'working' version od rodmcleay's post. I believe his version is easier to follow than my own, there were a couple of typos in his post EG: PhonNumberOut should have been PhoneNumberOut. Anyhow, this will work for all three of your given phone number formats:

--------------------cut n paste start----------------------------
<%
function formutPhoneNumber(PhoneNumberIn)
Dim PhoneNumberOut
PhoneNumberOut = replace(PhoneNumberIn,"(","")
PhoneNumberOut = replace(PhoneNumberOut,")","")
PhoneNumberOut = replace(PhoneNumberOut,"-","")

formutPhoneNumber = left(PhoneNumberOut,3) + "-"
formutPhoneNumber = formutPhoneNumber & mid(PhoneNumberOut,4,3) + "-"
formutPhoneNumber = formutPhoneNumber & right(PhoneNumberOut,4)
end function

dim numvar,numvar2,numvar3
numvar = "(123)123-4567"
numVar2 = "(123)-123-4567"
numVar3 = "(123)1234567"

response.write numVar & "=B4 function<bR>"
response.write formutPhoneNumber(numVar) & "=After function<br><br>"

response.write numVar2 & "=B4 function<bR>"
response.write formutPhoneNumber(numVar2) & "=After function<br><br>"

response.write numVar3 & "=B4 function<bR>"
response.write formutPhoneNumber(numVar3) & "=after function<br><br>"

'NOTE: I have created three variables (numvar,numvar2,numvar3)holding
'values equal to your example numbers. To format the number value in your
'variable called rstSearch2("unittelephone") replace:

'response.write numVar & "=B4 function<bR>"
'response.write formutPhoneNumber(numVar) & "=After function<br><br>"

'WITH

'response.write rstSearch2("unittelephone") & "=B4 function<bR>"
'response.write formutPhoneNumber(rstSearch2("unittelephone")) & "=After function<br><br>"
%>
-----------------cut n paste finish----------------------------------

;;;excuse me if i look "stupid" but were will i put that function? in my code,

No question is stupid, thats what forums are all about. I put my functions in an external file called functions.asp (NOT .INC - this is a security risk) and then place the include at the top of my page:



In saying this you can put your functions anywhere, if you place it on the page, put it at the top within <% %> tags of course


Wind is your friend
Matt

Thanks for the function script above it works but not

is there a way to use this code with a if statement ie:

if phonenumber is (123) 123-1234 then



else if
phonenumber is (123)1234567 then

else if
phonenumber is 123-123-1234

then

end if


__________________________

I need help with the if statements (what to enter as the statement) and also for the adjusted code to reflect that statement.

Hope this is clear for you guys thanks in advaced for your help.

netcrawler

I think you should do some research on functions, how they are used, how they are tiggered etc...

;;;is there a way to use this code with a if statement

Why would you want to? just pass the phone numbers into the function. Have you tryed this? You say:

;;;if phonenumber is (123) 123-1234
and
;;;if phonenumber is (123)1234567
and
;;;phonenumber is 123-123-1234

Are you going to place an if here for every phone number?
Most of them would be unique are they?
If the answer to the two questions above is yes, you would be quicker to change then manualy via direct database access - obviously a bad idea.

I will try to explain again:
1..Get your records in a record set (all of the phone numbers) I believe you have them in a record set called rstSearch2?
2..if so, rstSearch2("unittelephone") should be holding the phone number value
3..Now loop through the record set, pass ALL the phone numbers into the function (only needs to be written once on the page) and then re insert them. This way you dont need to run this function EVERY time you pull the records out in the future. You can correct the inconsistant format and forget about the poor validation that made them this way.

NOTE: this is not cut n paste because I dont know your table name and the name of your unique database field for this table. The following asumes:
A..you have a unique field called ID
B..The name of your table is tbleName
C..You have a DB connection and rstSearch2 has been dimmed
D..The function is somewhere on your page and the name remains the same
<%
'get records
sql = "SELECT id, unittelephone FROM tbleName;"
set rstSearch2 = conn.execute(sql)
DIM newNum
'check is empty
IF NOT rstSearch2.EoF
   'loop through record set
   DO UNTILL rstSearch2.EoF
      'run function
      newNum = formutPhoneNumber(rstSearch2("unittelephone"))
      're insert newly formatted number
      sql = "INSERT INTO tbleName SET unittelephone = '" & newNum & "' WHERE id=" & rstSearch2("id")) & ";"
      'move to next record
      rstSearch2.moveNext
   LOOOP
ELSE
   response.write "Record set empty"
END IF


Wind is your friend
Matt