Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 8 of 8 total |
Changing Part of a string |
Mon, Jun 9 2008 8:04 PM | Permanent Link |
"Adam H." | 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. |
Mon, Jun 9 2008 9:28 PM | Permanent Link |
Jan Ferguson Data Software Solutions, Inc. Team Elevate | 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? |
Mon, Jun 9 2008 10:45 PM | Permanent Link |
"Jeff Cook" | 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 |
Tue, Jun 10 2008 2:41 AM | Permanent Link |
"Adam H." | 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. |
Tue, Jun 10 2008 2:41 AM | Permanent Link |
"Adam H." | 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. |
Tue, Jun 10 2008 3:29 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Tue, Jun 10 2008 6:52 PM | Permanent Link |
"Adam H." | 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. |
Wed, Jun 11 2008 5:02 PM | Permanent Link |
"Jeff Cook" | 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 |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |