Rich, With a negative number SSUB uses spaces as the delimiter: set var vTest1 text = (SSUB(.vTest,-1))
Ben Petersen On 11 Jun 2002, at 13:09, Richard Starkey wrote: > Sami, > > I forgot to mention. > > You would have to do a (SRPL(.fTest,(CHAR(32)),','0)) to change the char 32 > to a comma as the delimiter and then use the SSUB function. > > I would almost bet there is an easier way but this is all I can come up > with. > > Rich > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of Richard Starkey > Sent: Tuesday, June 11, 2002 12:52 PM > To: [EMAIL PROTECTED] > Subject: RE: Removing blank from data > > > 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 > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of Sami Aaron > Sent: Tuesday, June 11, 2002 11:50 AM > To: [EMAIL PROTECTED] > 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/ > > > > ================================================ > 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/ > ================================================ 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/
