Hi,

I'm wondering if the following is possible to do with SQL and DBISam.

Let's say I have a string field with a length of 10. I would like to change
the second character in the field.

ie - let's say I have 3 records with the following values:

ABCDEFG
AAAAAAA
BBBBBBB

And I want to change the second character to 'X', but leave everything else
the same ( so I would have

AXCDEFG
AXAAAAA
BXBBBBB

Is this posisble to do with SQL?

Thanks & Regards

Adam.

Adam,

I am not a SQL expert however it appears to me that it would need a
compbination of locating the second character in your example using the
SQL SUBSTRING statement and then using the SQL REPLACE statement would
accomplish what you wish to do.

Having said that (and to answer your question) DBISAM does not have a
SQL SUBSTRING or SQL REPLACE statement, although ElevateDB does for
both. However SQL itself does. For example, if you were to use the SQL
code of:

SELECT * FROM "CUSTOMER"
WHERE SUBSTRING(TestString FROM 1 FOR 1) = 'A'

and then use the REPLACE statement (an example is shown below)

SELECT REPLACE('ABCDEABCDE','CDE','xxx');

the result set would be:

ABxxxABxxx

Disclaimer: I am somewhat of a neophyte with SQL but I am aware of
these statements. Your mileage may vary. I'm not sure if this is
exactly what you are looking for but hopefully it's a step in the right
direction for you.

--
Regards,
Jan Ferguson [Team Elevate]


Adam H. wrote:

<<I'm wondering if the following is possible to do with SQL and DBISam.
<<ie - let's say I have 3 records with the following values:
<<
<<ABCDEFG
<<AAAAAAA
<<BBBBBBB
<<
<<And I want to change the second character to 'X', but leave
<<everything else the same ( so I would have
<<
<<AXCDEFG
<<AXAAAAA
<<BXBBBBB
<<
<<Is this posisble to do with SQL?

Adam H. wrote:

> Hi,
>
> I'm wondering if the following is possible to do with SQL and DBISam.
>
> Let's say I have a string field with a length of 10. I would like to
> change the second character in the field.
>
> ie - let's say I have 3 records with the following values:
>
> ABCDEFG
> AAAAAAA
> BBBBBBB
>
> And I want to change the second character to 'X', but leave
> everything else the same ( so I would have
>
> AXCDEFG
> AXAAAAA
> BXBBBBB
>
> Is this posisble to do with SQL?
>
> Thanks & Regards
>
> Adam.

UPDATE MYTable

SET MyField = SUBSTRING(MyField FROM 1 FOR 1) + 'X'
+ SUBSTRING(MyField FROM 3)

Howzat?

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com

Hi Jan,

Thanks for your reply...

> SELECT * FROM "CUSTOMER"
> WHERE SUBSTRING(TestString FROM 1 FOR 1) = 'A'
>
> and then use the REPLACE statement (an example is shown below)
>
> SELECT REPLACE('ABCDEABCDE','CDE','xxx');
>
> the result set would be:
>
> ABxxxABxxx

Unfortunately that won't work in my case, as what I'm wanting to replace is
position basesd, and not result based. (ie, in your example, I may only want
to replace the first CDE with XXX and not the second repetative CDE.

Best Regards

Adam.

Hi Jeff,

Thanks for your reply...

> UPDATE MYTable
>
> SET MyField = SUBSTRING(MyField FROM 1 FOR 1) + 'X'
> + SUBSTRING(MyField FROM 3)

Looks great - except the SET command is erroring out. (I'm assuming because
SUBSTRING isn't part of DBISam). Might work in EDB, but not DBISam.

> Howzat?

I'm trying to be nice, and holding back on the Aus/NZ cricket jokes at the
moment.  (although there's so many I have... it's hard)   

Cheers

Adam.

Adam


>Looks great - except the SET command is erroring out. (I'm assuming because
>SUBSTRING isn't part of DBISam). Might work in EDB, but not DBISam.

It is. UPDATE companies set _Name =  SUBSTRING(_Name,1,1)+'X'+SUBSTRING(_Name,3) works happily in V4.25

Try a select first to sort out your typing and then just cut'n'paste

Roy Lambert [Team Elevate]

Good Morning Roy and Jeff,

Thanks for your replies!

> It is. UPDATE companies set _Name =
> SUBSTRING(_Name,1,1)+'X'+SUBSTRING(_Name,3) works happily in V4.25

Correct - I missed the update part of the example. Good thing I didn't make
any NZ cricket jokes, or I could have had egg on my face like a normal
Australian!  

Cheers

Adam.

Adam H. wrote:


> Good thing I
> didn't make any NZ cricket jokes, or I could have had egg on my face
> like a normal Australian!  
>
> Cheers
>
> Adam.


You could have got away with any jokes you liked at the moment - NZ
cricket team is getting done over by the Poms (yep we can't even beat
England!)

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com