How to do this ?

#11

Hi Robert,

Answers inline....

"Robert Molyneux" wrote...

Quote:

I think there is a reasonable standard, namely AreaCode FourDigits
FourDigits for fixed phones and 4 digits 3 digits digits for mobile
phones.

for example 02 9123 4567 (8 digits or 10 digits)

well most people use (02) 9123 4567
and of course there are legacy phone numbers like (046) 123 5678

Quote:

and
0413 422 431 (10 digits)
for cell / mobile phones.

Some people use 041 342 2431 believe it or not !!

Of course you will have noticed we are slowly getting the American style of
nos such as 123-TAXI.

Quote:

The problem is with the various ways human beans can muck up a simple
system.

Couldn't agree more. Last year I contacted the relevant government body to
suggest they might like to consider a standard but it appears that it is all
too hard (Just like my quest to get banks to become Ebay friendly allowing
for 2 plus digit Item Nos for deposits - also too hard). I guess the
frustration is that computers have been around forever and still it feels
like the dark ages.

Quote:

I think 04 is restricted to mobile phones.

So why not:

It was a good suggestion but this is not my data to mess around with so
changing the values is a no-no. Basically, we have decided to get rid of
most of our composite secondary keys and create a Index table - basically
de-normalising the data but the beauty is that we can format these fields
however we want and for phone numbers it will be Alphanumeric only which is
good enough for a ..123.. QBE for example.

Quote:

And then, using a VOIP phone and an autodial modem, you could try
automatically dialling each number to see whether it is still valid. If it
rings once, OK, otherwise mark it as a dud.

Well given that quite a few of my customers are in the bush they are lucky
to have a working landline let alone mobiel and forget ADSL.

Leslie.

#12

"Ed Covney" <ccs-ed-was (AT) adelphia (DOT) net> wrote:

Quote:

All the phone numbers are in one text field? I envy you!

Nothing to envy, there are five or six phone numbers across
the many tables.

Quote:

I've seen users who use 2nd address lines for phone
numbers for no other reason than "That's were I like to
put 'em". ( I wish to heavens I was joking here.)

I've seen similar as well.
Leslie.

#13

Thanks Jan.

"janM" <bion (AT) skynet (DOT) be> wrote

Quote:

Hey Leslie,

Here is a way to do it:

;-----------------
method cmReplaceStringContent(strToCorrectString string,
strToReplace string,
strReplaceWith string) string
var
strBGN, strEND string
endvar
try
if strToCorrectString.advMatch("^(..)" + strToReplace +
"(..)$",
strBGN,
strEND) then

return cmReplaceStringContent(strBGN,
strToReplace,
strReplaceWith) +
strReplaceWith +
cmReplaceStringContent(strEND,
strToReplace,
strReplaceWith)

else

return strToCorrectString

endif
onfail
errorclear()
return strToCorrectString
endtry
endMethod
;------------------------
;Example:

method pushButton(var eventInfo Event)
var
arToStrip Array[] string
strInput string
strResult string
liLoop longint
endvar
try
;TESTING INPUT STRING:
strInput = "L !@es `l*(i ~9{8%5_&e M<>?,2)8./-i l$b|=[]u = 3^r 5+}\\8#n"

;characters to strip"~`!@#$%^&*()_+-=[]{}\\|;:'\",./<>? "
;initialize
strResult = ""
;optional
arToStrip.empty()

;fill the array with characters to strip
;arToStrip.addLast("~") ; this character first stripped before "for" loop
;special opal characters preceed with "\\"
arToStrip.addLast("`")
arToStrip.addLast("!")
arToStrip.addLast("\\@")
arToStrip.addLast("#")
arToStrip.addLast("\\$")
arToStrip.addLast("%")
arToStrip.addLast("\\^")
arToStrip.addLast("&")
arToStrip.addLast("\\*")
arToStrip.addLast("\$")
arToStrip.addLast("\$")
arToStrip.addLast("_")
arToStrip.addLast("\\+")
arToStrip.addLast("-")
arToStrip.addLast("=")
arToStrip.addLast("\\[")
arToStrip.addLast("\\]")
arToStrip.addLast("{")
arToStrip.addLast("}")
arToStrip.addLast("\\\\")
arToStrip.addLast("\\|")
arToStrip.addLast(";")
arToStrip.addLast(":")
arToStrip.addLast("'")
arToStrip.addLast(chr(34)) ;"\\"" or chr(34)
arToStrip.addLast(",")
arToStrip.addLast(".")
arToStrip.addLast("/")
arToStrip.addLast("<")
arToStrip.addLast(">")
arToStrip.addLast("\\?")
arToStrip.addLast(chr(32)) ;space

;first input the strInput
strResult = cmReplaceStringContent(strInput, chr(126), "") ;~

;loop through the rest of the string to strip further with characters from
the array
for liLoop from 1 to arToStrip.size()
strResult = cmReplaceStringContent(strResult, arToStrip[liLoop], "")
endfor

strResult.view() ;result = Lesli985eM28ilbu3r58n
onfail
errorclear()
endtry
endMethod

;--------------

Jan

#14

I think you are confusing the stored data with the display format, which
varies depending on user requirements and circumstances.

You don't need to change the original data (but I don't understand what you
are trying to achieve if you cannot do what is needed with it).

All phone numbers can be stored as a string of numbers, no spaces, no
brackets.

I had forgotten the 123 and 800 and 1800 ranges - but it just reinforces the
point.

I suggest that the way to handle this would be to keep the data as a string
of numbers, and develop a display format capability.

So for example the 123-TAXI number would be stored as 1238294 (which you
need to know anyway) with a dispay format indicator to display the last 4
digits as a mnemonic word. Given that there are three or four characters per
digit, you need to store TAXI as well.

In Australia, 9123 4567 becomes 02 9123 4567 if the number is in NSW or 08
9123 4567 if the number is in Western Australia or 03 9123 4567 if the
number is in Victoria or (just to be annoying) in NSW but close to the
Victorian border...

And if you want to include international dialing, it becomes 61 - 2 - 9123
4567 (that is, the leading 0 is dropped).

"Leslie Milburn" <CDB4W (AT) NOSPAM (DOT) bigpond.com> wrote

Quote:

Hi Robert,

Answers inline....

"Robert Molyneux" wrote...
I think there is a reasonable standard, namely AreaCode FourDigits
FourDigits for fixed phones and 4 digits 3 digits digits for mobile
phones.

for example 02 9123 4567 (8 digits or 10 digits)

well most people use (02) 9123 4567
and of course there are legacy phone numbers like (046) 123 5678

and
0413 422 431 (10 digits)
for cell / mobile phones.

Some people use 041 342 2431 believe it or not !!

Of course you will have noticed we are slowly getting the American style
of
nos such as 123-TAXI.

The problem is with the various ways human beans can muck up a simple
system.

Couldn't agree more. Last year I contacted the relevant government body to
suggest they might like to consider a standard but it appears that it is
all
too hard (Just like my quest to get banks to become Ebay friendly allowing
for 2 plus digit Item Nos for deposits - also too hard). I guess the
frustration is that computers have been around forever and still it feels
like the dark ages.

I think 04 is restricted to mobile phones.

So why not:

It was a good suggestion but this is not my data to mess around with so
changing the values is a no-no. Basically, we have decided to get rid of
most of our composite secondary keys and create a Index table - basically
de-normalising the data but the beauty is that we can format these fields
however we want and for phone numbers it will be Alphanumeric only which
is
good enough for a ..123.. QBE for example.

And then, using a VOIP phone and an autodial modem, you could try
automatically dialling each number to see whether it is still valid. If
it
rings once, OK, otherwise mark it as a dud.

Well given that quite a few of my customers are in the bush they are lucky
to have a working landline let alone mobiel and forget ADSL.

Leslie.

#15

Quote:

We are trying to achieve a method to search via phone number including a
partial match, and thats the problem.

Leslie -

No matter what format it is, why not create another
field to host a fully stripped phone number? When
searching, strip the search criteria similarly and
search the fully stripped field instead of the loosely
formatted one.

This way your users get to see the phone numbers in
their favorite colage of formats .. and you'll be able to
provide a search that will amuse and amaze.

For ex:

9123 4567 becomes '91234567'
9 123 4567 becomes '91234567'
02 9123 4567 becomes '0291234567'
or even
9 123 4567 Ext 127 becomes '91234567127'
(if you want users to be able to search ext's also)

Then create your own search routine that allows users
to input '123 45 67' but changes their input to '1234567'
before searching the new stripped field.

Ed

#16

"Ed Covney" <ccs-ed-was (AT) adelphia (DOT) net> wrote

Quote:

No matter what format it is, why not create another
field to host a fully stripped phone number? When
searching, strip the search criteria similarly and
search the fully stripped field instead of the loosely
formatted one.

Hi Ed,

Yes, thats exactly how we are doing it :-)

We have created a new table IXSEARCH which contains the compacted copy of
the Phone Number fields. This means that all the phone number fields are in
the one place rather than 8 or so tables which makes searching trivial.

The benefit of such an approach as mentioned previously is that we have now
been able to eliminate a number of composite secondary keys by
de-normalising and placing those values into the IXSEARCH table as well.

And so we return to the original question of how to remove punctuation (or
any sequence of characters) from a field via an SQL query. Using tcursors to
traverse the various tables will be very slow because of the tables sizes
and number of them.

To be honest I was surprised I couldn't think of a way to do it with SQL and
thus my hope that someone here does.
Leslie.

#17

Leslie Milburn wrote:

Quote:

And so we return to the original question of how to remove punctuation
(or any sequence of characters) from a field via an SQL query.

I don't see how you'd do it with Local SQL. Other dialects could, but not
Local SQL. Load it into MySQL or Firebird or OracleXE.

--
Larry DiGiovanni
Digico, Inc.
IT Consulting and Staffing Solutions
www.digicoinc.com
Check out www.thedbcommunity.com for Paradox resources.