Board index paradox

Substr weirdness...

Substr weirdness...

Post by Mark Llewelly » Wed, 09 Jul 1997 04:00:00



Hi all,

First time I've ever seen this:  I'm trying to do a simple subStr
against an A 13 field called "HomePhone".  The data is stored in the
format "(000)123-4567".  I was trying to split out the area code, add a
space, then append the rest of the number.  I'm using a TCursor and code
as follows:

(open tcursor in edit mode)
scan tc:
        tc.City = tc.HomePhone.subStr(1, 5) + " " +   tc.HomePhone.subStr(6, 8)
;  tc.City is a dummy field to store    result, A 20
endscan
(etc.)

The error message I'm getting is:

String:Method:SubStr:
The value of the parameter, 'startIndex' is not legal.  The range is too
large.

I've also tried using a string variable to store the value of HomePhone
into, then working off of the variable.  Same error!

What am I missing here?  Thanks for any help!
Mark

Mark E. Llewellyn
United Airlines

(303) 779-2056

 
 
 
Top

Substr weirdness...

Post by Mike Thulee » Wed, 09 Jul 1997 04:00:00


Hi Mark:


> Hi all,

> First time I've ever seen this:  I'm trying to do a simple subStr
> against an A 13 field called "HomePhone".  The data is stored in the
> format "(000)123-4567".  I was trying to split out the area code, add a
> space,

If you are putting the new value in the same field, the field would need
to have 14 characters in it (I see that you are putting it in an A20
field).

Quote:>then append the rest of the number.  I'm using a TCursor and code
> as follows:

> (open tcursor in edit mode)
> scan tc:
>         tc.City = tc.HomePhone.subStr(1, 5) + " " +     tc.HomePhone.subStr(6, 8)

This doesn't have all of the phone number characters in it...out to the
13th character

try tc.City=tc.HomePhone.subStr(1,5)+" "+tc.HomePhone.subStr(6,13)

Quote:> ;  tc.City is a dummy field to store    result, A 20

Aaaaah...

Quote:> endscan
> (etc.)

> The error message I'm getting is:

> String:Method:SubStr:
> The value of the parameter, 'startIndex' is not legal.  The range is too
> large.

> I've also tried using a string variable to store the value of HomePhone
> into, then working off of the variable.  Same error!

> What am I missing here?  Thanks for any help!
> Mark

Please try this to see if it works for you:

method run(var eventInfo Event)
var
   tc           TCursor
   s1,s2        String
   tv           TableView
endVar

tc.open("Phonetable.db")      ;open the tcursor
tc.edit()                       ;put the tcursor in editmode
tc.bot()                        ;set the tcursor to the beginning of the                        
;table

scan tc :

s1=tc.HomePhone.subStr(1,5)     ;get characters 1 through 5
s2=tc.HomePhone.subStr(6,13)    ;get characters 5 through 13
tc.City=s1+" "+s2             ;put characters 1-5 and 6-13 together                  
;with the space

endScan

tc.endEdit()                 ;end tcursor edit mode
tc.close()                   ;close tcursor

tv.open("Phonetable.db")      ;check the table to see if                                              ;changes made

endMethod

Respectfully,

Mike Thuleen

Boise, ID
Quattro Pro/Paradox/Delphi Programming Services

 
 
 
Top

Substr weirdness...

Post by Al Solte » Wed, 09 Jul 1997 04:00:00



> Hi all,

> First time I've ever seen this:  I'm trying to do a simple subStr
> against an A 13 field called "HomePhone".  The data is stored in the
> format "(000)123-4567".  I was trying to split out the area code, add a
> space, then append the rest of the number.  I'm using a TCursor and code
> as follows:

> (open tcursor in edit mode)
> scan tc:
>         tc.City = tc.HomePhone.subStr(1, 5) + " " +     tc.HomePhone.subStr(6, 8)
> ;  tc.City is a dummy field to store    result, A 20
> endscan
> (etc.)

> The error message I'm getting is:

> String:Method:SubStr:
> The value of the parameter, 'startIndex' is not legal.  The range is too
> large.

> I've also tried using a string variable to store the value of HomePhone
> into, then working off of the variable.  Same error!

> What am I missing here?  Thanks for any help!
> Mark

> Mark E. Llewellyn
> United Airlines

> (303) 779-2056

Your code looks fine.  Check your data.  You either have an empty field
or one with less than 6 characters in it (ie: 911).

Al Solter

 
 
 
Top

Substr weirdness...

Post by We » Thu, 10 Jul 1997 04:00:00





>> (open tcursor in edit mode)
>> scan tc:
>>         tc.City = tc.HomePhone.subStr(1, 5) + " " +    

tc.HomePhone.subStr(6, 8)

Quote:>> ;  tc.City is a dummy field to store    result, A 20
>> endscan
>> (etc.)

>> The error message I'm getting is:

>> String:Method:SubStr:
>> The value of the parameter, 'startIndex' is not legal.  The range is too
>> large.

>Your code looks fine.  Check your data.  You either have an empty field
>or one with less than 6 characters in it (ie: 911).

>Al Solter

I agree. I think there is an empty record somewhere in your table. Your code
should read:

if size(tc.homePhone)>=6 then
  tc.City = tc.HomePhone.subStr(1, 5) + " " +     tc.HomePhone.subStr(6, 8)
endif

 
 
 
Top

Substr weirdness...

Post by Dinis de Brito e Cun » Thu, 10 Jul 1997 04:00:00


: Hi all,

: First time I've ever seen this:  I'm trying to do a simple subStr
: against an A 13 field called "HomePhone".  The data is stored in the
: format "(000)123-4567".  I was trying to split out the area code, add a
: space, then append the rest of the number.  I'm using a TCursor and code
: as follows:

: (open tcursor in edit mode)
: scan tc:
:       tc.City = tc.HomePhone.subStr(1, 5) + " " +   tc.HomePhone.subStr(6, 8)
: ;  tc.City is a dummy field to store  result, A 20
: endscan
: (etc.)

: The error message I'm getting is:

: String:Method:SubStr:
: The value of the parameter, 'startIndex' is not legal.  The range is too
: large.

: I've also tried using a string variable to store the value of HomePhone
: into, then working off of the variable.  Same error!

On the first attempt I would say that you stored a phone-number with less
then 8 characters. Did you verify that there is no phone-number with less
than 8 characters? Sorry for that stupid question!

wfg Dinis

 
 
 
Top

Substr weirdness...

Post by Peter Join » Thu, 10 Jul 1997 04:00:00


Quote:

> I agree. I think there is an empty record somewhere in your table. Your
code
> should read:

> if size(tc.homePhone)>=6 then
>   tc.City = tc.HomePhone.subStr(1, 5) + " " +     tc.HomePhone.subStr(6,
8)
> endif

shouldn't that be?
 if size(tc.homePhone)>=14 then
   tc.City = tc.HomePhone.subStr(1, 5) + " " +     tc.HomePhone.subStr(6,
8)
 endif
Regards,
Vaggs

----------------------------------
Peter Joint
Joint Computers Pty. Ltd.

 
 
 
Top

Substr weirdness...

Post by dmorga » Thu, 10 Jul 1997 04:00:00


Quote:> : scan tc:
> :  tc.City = tc.HomePhone.subStr(1, 5)+" "+tc.HomePhone.subStr(6, 8)
> : endscan

Well what is wrong with this is the field name. Try tc."City" and
tc."HomePhone" (in other words put quotes around it).

Daniel A. Morgan

 
 
 
Top

Substr weirdness...

Post by Al Solte » Thu, 10 Jul 1997 04:00:00



> > : scan tc:
> > :  tc.City = tc.HomePhone.subStr(1, 5)+" "+tc.HomePhone.subStr(6, 8)
> > : endscan

> Well what is wrong with this is the field name. Try tc."City" and
> tc."HomePhone" (in other words put quotes around it).

> Daniel A. Morgan

Dan,

The only time you need to use quotation marks here is if you have
imbedded a space in the field name.

Al

 
 
 
Top

Substr weirdness...

Post by We » Sat, 12 Jul 1997 04:00:00




Quote:

>> I agree. I think there is an empty record somewhere in your table. Your
>code
>> should read:

>> if size(tc.homePhone)>=6 then
>>   tc.City = tc.HomePhone.subStr(1, 5) + " " +     tc.HomePhone.subStr(6,
>8)
>> endif

>shouldn't that be?
> if size(tc.homePhone)>=14 then
>   tc.City = tc.HomePhone.subStr(1, 5) + " " +     tc.HomePhone.subStr(6,
>8)
> endif
>Regards,
>Vaggs

No, not to simply eliminate the error. ONLY the startindex of a substr() has
to be <= the size() of the string. If more characters are requested than what
is available, then whatever is available is returned. No error occurs. So to
test that the phone number is at least 6 characters long ensures no error
occurs when the tc.HomePhone.substr(6,8) is evaluated.

I guess you could say that this might lead to a logical bug and you would be
correct.

FOR DANIEL:

Daniel, " " are only needed when a field name contains a space. I never use it
otherwise.

 
 
 
Top

Substr weirdness...

Post by We » Sat, 12 Jul 1997 04:00:00




Quote:

>> I agree. I think there is an empty record somewhere in your table. Your
>code
>> should read:

>> if size(tc.homePhone)>=6 then
>>   tc.City = tc.HomePhone.subStr(1, 5) + " " +     tc.HomePhone.subStr(6,
>8)
>> endif

>shouldn't that be?
> if size(tc.homePhone)>=14 then
>   tc.City = tc.HomePhone.subStr(1, 5) + " " +     tc.HomePhone.subStr(6,
>8)
> endif
>Regards,
>Vaggs

No, not to simply eliminate the error. ONLY the startindex of a substr() has
to be <= the size() of the string. If more characters are requested than what
is available, then whatever is available is returned. No error occurs. So to
test that the phone number is at least 6 characters long ensures no error
occurs when the tc.HomePhone.substr(6,8) is evaluated.

I guess you could say that this might lead to a logical bug and you would be
correct.

FOR DANIEL:

Daniel, " " are only needed when a field name contains a space. I never use it
otherwise.

 
 
 
Top

Substr weirdness...

Post by Mark Llewell » Sun, 13 Jul 1997 04:00:00






>>Your code looks fine.  Check your data.  You either have an empty field
>>or one with less than 6 characters in it (ie: 911).

>>Al Solter

>I agree. I think there is an empty record somewhere in your table. Your code
>should read:

>if size(tc.homePhone)>=6 then
>  tc.City = tc.HomePhone.subStr(1, 5) + " " +     tc.HomePhone.subStr(6, 8)
>endif

Thanks everyone!  This is similar to the code I ended up using.

-Mark-

 
 
 
Top

1. dbms_lob.substr weirdness with clobs

Hi,

I am encountering some very strange behaviour trying to manipulate
CLOB data in Oracle9i, and hoped someone here might have already found
a solution.

I am working with data that is around 20k-50k in size, and want to
break it up into smaller CLOB chunks.  I can select the CLOB data
fine, and I have checked the math, as well as doing some manual
testing in SQL to verify what I am doing, and as far as I can tell, it
should work.

The problem is that when I try to select a portion of the CLOB data
using the dbms_lob.substr function, it returns most, but not all of
the specified information, even though the source CLOB has much more
than is being selected.

Here is my process:

Locate start position using dbms_lob.instr():

x := dbms_lob.instr(src_clob,'<TAG>') + 5;

This give me the correct starting byte position within the source
CLOB, using SQL*Plus and real data in the database in testing.

Next, I locate the end position using dbms_lob.instr() again, and
calculate the size of the data I am extracting:

y := dbms_lob.instr(src_clob,'<TAG2>') - 1 - x;
if y <= 0 then
    y := dbms_lob.getlength(src_clob) - x;
end if;

Again, I have verified this value using real data, and SQL*Plus, and
have proven it is correct.

Next, I extract the CLOB substring using:

dest_clob := dbms_lob.substr(src_clob, y, x);

Then, I update the value in the database using:

select src_clob into tmp_clob
from table_name
where primarykey = ID
for update;

update table_name
set src_clob = dest_clob
where primarykey = ID;

This too seems to work fine, but when I query the src_clob, I find
that most of the data (around 16k in my test case) is there, starting
at the correct position, but the end of the text is around 200 bytes
short of the position it is supposed to be at!

What gives?  Is this a bug?  Am I missing something?

Any thoughts or suggestions would be appreciated.

Thanks in advance!

Bjorn

2. Layout Question

3. ANN: TaoADODataSet v1.43 has been released

4. REPOST: dbms_lob.substr weirdness with clobs

5. Installing IDS.2000 on Redhat

6. left right substr

7. Hello Eveybody...A unique situation here...

8. Update with substr()

9. sql statement not able to recognise substr()

10. SQL substr equivalent!

11. Missing SUBSTR function in Interbase?

12. Jet SQL - SUBSTR clause


All times are UTC
Board index