Try using a view.
The first view is an autonumbered list of values.
Additional views are created for each position.
A final view puts them all together.
-- uniquely number the rows with autonum ssnID column
DROP VIEW vwSSN
CREATE VIEW vwSSN (ssnID, ssnTxt) +
AS SELECT ssnID,(ssn) FROM SSN
-- create a view for each position
DROP VIEW vwSSN1
CREATE view vwSSN1 (ssnID, p1) AS +
SELECT ssnID, (IFEQ( (ISDIGIT( (SGET(ssn,1,1)) )),1, +
(SGET(ssn,1,1)),NULL ) ) FROM ssn
-- essentially: If the first character of ssn is a digit, put in
the digit, otherwise put in a null
DROP VIEW vwSSN2
CREATE view vwSSN2 (ssnID, p2) AS +
SELECT ssnID, (IFEQ( (ISDIGIT( (SGET(ssn,1,2)) )),1, +
(SGET(ssn,1,2)),NULL ) ) FROM ssn
-- If the second character of ssn is a digit, put in
the digit, otherwise put in a null... and so forth
DROP VIEW vwSSN3
CREATE view vwSSN3 (ssnID, p3) AS +
SELECT ssnID, (IFEQ( (ISDIGIT( (SGET(ssn,1,3)) )),1, +
(SGET(ssn,1,3)),NULL ) ) FROM ssn
DROP VIEW vwSSN4
CREATE view vwSSN4 (ssnID, p4) AS +
SELECT ssnID, (IFEQ( (ISDIGIT( (SGET(ssn,1,4)) )),1, +
(SGET(ssn,1,4)),NULL ) ) FROM ssn
DROP VIEW vwSSN5
CREATE view vwSSN5 (ssnID, p5) AS +
SELECT ssnID, (IFEQ( (ISDIGIT( (SGET(ssn,1,5)) )),1, +
(SGET(ssn,1,5)),NULL ) ) FROM ssn
DROP VIEW vwSSN6
CREATE view vwSSN6 (ssnID, p6) AS +
SELECT ssnID, (IFEQ( (ISDIGIT( (SGET(ssn,1,6)) )),1, +
(SGET(ssn,1,6)),NULL ) ) FROM ssn
-- put the valid number digits together
DROP VIEW vwSSN1_6
CREATE VIEW vwSSN1_6 (ssnID, ssnText) +
AS SELECT ssnID, (p1 + p2 + p3 + p4 + p5 + p6) +
FROM vwssn1 t1, +
vwssn2 t2, +
vwssn3 t3, +
vwssn4 t4, +
vwssn5 t5, +
vwssn6 t6 +
WHERE +
(t1.ssnID = t2.ssnID) AND +
(t1.ssnID = t3.ssnID) AND +
(t1.ssnID = t4.ssnID) AND +
(t1.ssnID = t5.ssnID) AND +
(t1.ssnID = t6.ssnID)
RETURN
FOR SSN:
ssnID SSN
---------- ----------------
1 223-33-0985
2 A330 55 8839
3 558N02M-3328
vwSSN1_6:
ssnID ssnText
---------- ---------------
1 22333
2 3305
3 55802
Create a view for each position to the maximum characters of SSN.
(If you put the calculations for each position together in one view you
wouldn't need the autonumbered column or the correlations at the end.)
-- although the following is tempting, it exceeds the allowable expression
size:
DROP VIEW vwSSN2
CREATE VIEW vwSSN2 (ssnID, ssnTxt) +
AS SELECT ssnID, ( +
(IFEQ( (ISDIGIT( (SGET(ssn,1,1)) )),1, +
(SGET(ssn,1,1)),NULL ) ) + +
(IFEQ( (ISDIGIT( (SGET(ssn,1,2)) )),1, +
(SGET(ssn,1,2)),NULL ) ) + +
(IFEQ( (ISDIGIT( (SGET(ssn,1,3)) )),1, +
(SGET(ssn,1,3)),NULL ) ) + +
(IFEQ( (ISDIGIT( (SGET(ssn,1,4)) )),1, +
(SGET(ssn,1,4)),NULL ) ) + +
(IFEQ( (ISDIGIT( (SGET(ssn,1,5)) )),1, +
(SGET(ssn,1,5)),NULL ) ) ) +
FROM SSN
--
Randy Peterson
Lawrence Lustig wrote:
> > I want to compare JUST the NUMBERS entered into the field, so in this
> case,
> > I want to get the value to:
> >
> > 00051334944
>
> Sami:
>
> I have a DLL that will do this for you. Let me know if you want it.
> --
> Larry
>
> ================================================
> 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/
================================================
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/
