Hello,

I've got help here before and I need some again. I'm using DBIsam 2.05.

Given a text field value in the following format:

12 45678 98765

I need to update/reformat this filed value to:

1245678 9 8765

for all field values in the table.

Thanks for your assistance.

Thomas

Thomas,

<< I've got help here before and I need some again. I'm using DBIsam 2.05.

Given a text field value in the following format:

12 45678 98765

I need to update/reformat this filed value to:

1245678 9 8765

for all field values in the table. >>

Unfortunately, you can only do this with navigational methods in 2.x since
the SQL string functions weren't developed enough to handle formatting
numbers like that.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Thomas,

<< I've got help here before and I need some again. I'm using DBIsam 2.05.

Given a text field value in the following format:

12 45678 98765

I need to update/reformat this filed value to:

1245678 9 8765

for all field values in the table. >>

Unfortunately, you can only do this with navigational methods in 2.x since
the SQL string functions weren't developed enough to handle formatting
numbers like that.

--
Tim Young
Elevate Software
www.elevatesoft.com


Thanks for the reply Tim.

I should have pointed out  that the 12 45678 is constant in all field values, its the last 5 digits are variable.
With the string 12 45678 98765 and using -- UPDATE history SET fldstring=REPLACE('12 ' WITH '12' IN fldstring) -- gets me to the string 1245678 98765.

To get a space between the 9 and the 8 in 98765 so that it looks like 9 8765 I have tried this much so far:

select SUBSTRING(fldstring FROM 13) from history

This returns the last 4 digits e.g. 8765

Is there a way after this select that i could prepend a space and the write it back?

Any guidance appreciated and I respect I am limited with 2.05 but I have no choice.

Thanks,

Thomas

Thomas

> Given a text field value in the following format:
>
> 12 45678 98765
>
> I need to update/reformat this filed value to:
>
> 1245678 9 8765
>
> for all field values in the table.

Presuming you are trying to remove the first space and insert one after the
10th digit

How about

UPDATE history SET fldstring=SUBSTRING(fldstring FROM 1 for
2)+SUBSTRING(fldstring FROM 4 for 6)+SUBSTRING(fldstring FROM 10 for 1)+'
'+SUBSTRING(fldstring FROM 11)



John

Thomas,

<< I should have pointed out  that the 12 45678 is constant in all field
values, its the last 5 digits are variable. >>

Ahh, that's different.  In that case, you can use the SQL John has been
so kind as to post.

--
Tim Young
Elevate Software
www.elevatesoft.com