Using the following two views will do it:
-- Thanks to Larry Lustig at the Developers' Conference
-- for showing that views are versatile and powerful
-- this view takes the data field (ssn) and leaves only numbers
-- this also assumes that the field in your table has a variety of
-- of characters and spacing
-- Thanks to Ron Rose:
DROP VIEW vwssn02
CREATE VIEW vwssn02 (ssnid,ssn02) +
AS SELECT ssnid,(FORMAT(ssn,'99999999999999999999')) +
FROM ssn
-- this view removes all the spaces
-- Thanks to Rich Starkey:
DROP VIEW vwssn03
CREATE VIEW vwssn03 (ssnid, ssn03) +
AS SELECT ssnid, +
( (SSUB(ssn02,-1)) +
+ (SSUB(ssn02,-2)) +
+ (SSUB(ssn02,-3)) +
+ (SSUB(ssn02,-4)) +
+ (SSUB(ssn02,-5)) +
+ (SSUB(ssn02,-6)) +
+ (SSUB(ssn02,-7)) ) +
FROM vwssn02
RETURN
SSN:
ssnID SSN
---------- ----------------
1 223-33-0985
2 A330 55 8839
3 558N02M-3328
4 XA1M22L30j3885
vwSSN02:
ssnid ssn02
---------- ---------------
1 223 33 0985
2 330 55 8839
3 558 02 3328
4 1 22 30 3885
vwSSN03:
ssnid ssn03
---------- ---------------
1 223330985
2 330558839
3 558023328
4 122303885
Sami, try:
SELECT COUNT(*) INTO vcount FROM vwssn03 WHERE ssn03 = '558023328'
Randy Peterson
Ron Rose wrote:
> Did you try :
> SET VAR vSpace TEXT = CHAR(32)
> (SRPL((FORMAT(.ftest,'99999999999999999999'))),.vSpace,'',1))
> RRR
Rich Starkey wrote:
Sami,
The only thing I can think of is to use (SSUB(n)) after you use the
(FORMAT()) function.
set var vTest1 text = (SSUB(.vTest,1))
set var vTest2 text = (SSUB(.vTest,2))
set var vTest3 text = (SSUB(.vTest,3))
update table set fTest = (.vtest1 + .vTest2 + .vTest3)
It's a little cumbersome but I think this may work
Rich Starkey
And thank you Sami for the challenge.
>
> ----- Original Message -----
> From: "Sami Aaron" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, June 11, 2002 8:50 AM
> Subject: Removing blank from data
>
> > I am trying to compare data entered into a new record with existing data
> in the table.
> >
> > The data could be entered as:
> >
> > 00-051-334944
> > 00051334944
> > 00 051 334944
> > 00-051-334944CZA
> > Y 00 051-334944
> >
> > I want to compare JUST the NUMBERS entered into the field, so in this
> case, I want to get the value to:
> >
> > 00051334944
> >
> > then see if that value exists in the table, in any of the above formats.
> >
> > I can use the FORMAT function to strip out all non-numeric characters:
> > SET VAR vtest TEXT = (FORMAT(.ftest,'99999999999999999999'))
> >
> > However, this function leaves a blank (CHAR(32))? anywhere there are
> > non-numeric values. So, if the user entered, 00-051-334944, this function
> > would give me 00 051 334944.
> >
> > Does anyone know a function or one-step SET VAR command to eliminate the
> > remaining spaces within the field? I've tried the SRPL with the (CHAR(0))
> > and with the (CHAR(8)) (backspace) but to no avail.
> >
> > I want to be able to issue the command:
> >
> > SELECT COUNT(*) INTO vcount FROM table WHERE (format(columnname,something,
> > something)) = .vtest.
> >
> > example: SELECT COUNT(*) INTO vcount FROM table WHERE 00051334944 =
> > 00051334944
> >
> > Thanks,
> > Sami
> >
> > -----------------------------------------------------------
> > Sami Aaron
> > Software Management Specialists
> > 19312 W 63rd Terr
> > Shawnee KS 66218
> > 913-915-1971
> > http://www.softwaremgmt.com
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
