Mike: Here's what I tried on a column named REV_DESC which is a TEXT 20.
SELECT rev_desc,(SLEN((SPUT(rev_desc,(SFIL(CHAR(32),20)),1)))) FROM cw_specs_data WHERE rev_desc IS NULL The output showed the null values for REV_DESC and the SLEN had a value of 0. The function attempts to SFIL rev_desc with 20 CHAR(32)s starting at position 1. I simply embedded these functions inside of an SLEN to test the length which came out to 0. If I used any normal character it works correctly but it is, of course, not what I'm after. Thanks for the suggestion, though. I admire your abilities with VBSCRIPT and such -- actually I'm downright envious! ;-) I played around with this just a bit and found that if I used the same expression as above but on fields that are not null the function essentially strips off the first character of the field string. Mike -----Original Message----- From: MikeB [mailto:[EMAIL PROTECTED] Sent: Friday, November 07, 2003 2:43 PM To: [EMAIL PROTECTED] Subject: [RBASE-L] - Re: Fixed field export with embedded spaces (SPUT(text,string,pos)) Puts string into text, starting at pos, returning a text string Try SPUT..... ----- Original Message ----- From: "Ramsour Mike" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> Sent: Friday, November 07, 2003 2:36 PM Subject: [RBASE-L] - Fixed field export with embedded spaces > Greetings: > > I need to create an ASCII fixed field formatted file (say that 3 times real > fast). I am using the LJS function but am having an issue with fields that > are null. This may have been discussed before but is there a way to embed > space characters into a field when it is NULL? > > For instance: (LJS(IFNULL,<field>,' ',<field>),5)) > > which is to say that if the field is null embed five spaces otherwise use > the contents of the field. > > I ended up doing a workaround that embedded XXXXX if the field was null and > then I used R:Code to replace the XXXXX with 5 spaces in the output file. > Not exactly elegant but it worked. I've tried using the CHAR function, too, > but that didn't work unless I'm missing something. > > Any suggestions are greatly appreciated. > > Incidentally, I tried the same technique in 7.0 and got the same results. > > Thanks > > Mike Ramsour > Voice: 740-829-4340 > > Test: > > SET VAR vspace TEXT = (SFIL(' ',10)) -- trying to create a text variable > that has a length of 10 characters that are all spaces. Also tried using > CHAR(32) > -- > SET VAR vlen INTEGER = (SLEN(.vspace) -- to test the length of vspace > variable > -- > SHOW VAR vlen gives a result of 0 > -- > SET VAR vspace TEXT = (SFIL(' ',10) + '9') -- trying to create a text > variable that starts with 10 spaces and ends with a 9 > -- > SET VAR vlen INTEGER = (SLEN(.vspace) -- to test the length of vspace > variable > -- > SHOW VAR vlen gives a result of 11, i.e. 10 spaces plus 1 character > -- > >
