Sami, SRPL() doesn't allow a null or blank for it's last argument, which would solve the problem. My suggestion: write your own Stored Procedure that parses its text input parameter from left to right, keeping only the numeric characters, and building the return value you want. Then, you can do this:
SELECT COUNT(*) INTO vcount FROM table + WHERE (CALL KeepNumbersSP(columnname)) = .vtest When you're done, I might buy your SP from you, if the price is right. It will help greatly at standardizing text data for SSNs, phone numbers, etc, where some people type in the hyphens and some use spaces and some use periods, etc. Bill On Tue, 11 Jun 2002 10:50:06 -0500, Sami Aaron wrote: >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/ > > > >================================================ >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/
