Hi all,

I need to strip all punctation out of an alpha field leaving just the the
a-z, A-Z, and 0 - 9 characters.
Can this be done via a QBE or SQL Query ? and if so can anyone give an
example

Thanks
Leslie.

Leslie,

I suspect you'd have to use code (e.g. breakApart). Maybe SQL can do
it, but I can't think of how. QBE definitely cannot.

Liz


Leslie Milburn wrote:

"Liz McGuire" <liz (AT) paradoxcommunity (DOT) com> wrote:

Hi Liz,

Good to hear because my brain is fried trying to think of a way.

Leslie.

Leslie -

Be carefull what you wish for. Are you absolutely
sure you want to get rid of all spaces, puncuation
and formating as well? No matter what the desired
valid characters are - it seems like every character
must be tested. Assuming you can set up tcursor'ing
through your table, the meat (untested) is provided
below.

Ed


strValid = "0123456789abcd.." ; fully enumerate desireable characters
(open / edit tcursor)
....(start loop to move thru table 1 record at a time)

strIn = tcD."FieldName" ; field to be stripped
strOut = ""
for i from 1 to strIn.size()
strX = strIn.substr(i,1)
if strValid.search(strX) > 0 then ; Valid character
strOut = strOut + strX
endif
endfor
tcD."FieldName" = strOut
tcD.postrecord()

.... end loop ( & cleanup)

--------------



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

Ed,

Actually, you can use breakApart (assuming you know all the possible
invalid chars):

stBreak = "~`!@#$%^&*()_+-=[]{}\\|;:'\",./<>? "
;// backslash and double quote are escaped
stFixMe.breakApart(ar,stBreak)
stFixed.blank()
for li from 1 to ar.size()
stFixed = stFixed + ar[li]
endFor

....if you don't know all the possible invalids, then you'd have to do
like you described.

Liz


Ed Covney wrote:

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

Yes, tcursoring through the table was my fallback position mainly because it
will be slow. I would really like to do it in a query though but I cannot
think of how.

The need is due to the fact that the fields in question are free format
phone number fields and we want to do a partial match. So things like ()[]
and spaces will be an issue due to the inconsistency of data entry. What is
really a surprise is that in Australia there is no standard on the format of
phone number fields, so of course every company uses any gimmick they want.
I say surprise because in this day and age, I would have expected there to
be a standard (or a couple to choose from) just like dates.

Thanks
Leslie

Liz -
Agreed - probably much faster to boot.

I have a personal preference to go about such tasks
from an inclusionary rather than exclusionary point
of reference.

Ed



"Liz McGuire" <liz (AT) paradoxcommunity (DOT) com> wrote

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

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.)


Ed

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)

for line phones and fax numbers

and

0413 422 431 (10 digits)

for cell / mobile phones.

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

I think 04 is restricted to mobile phones.

So why not:

scan the number character by character and strip out anything that is not a
number 0-9 (use ASCII range) and pack the string;

anything startting with 04 and 10 digits long is probably a mobile phone.

anything starting with 0N (where N is not 4) and 10 digits long is probably
a fixed line with area code.

anything else and 8 digits long is probably a fixed line with no area code,
and you could determine this from the postal address, if available.

anything else is a dud number.

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.

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