By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,648 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

how to use replace and substring correctly.

P: 3
Hi, can someone please help me from my pit of despair....

I have a table - "Drawing" with a column "Contract" -varchar, which contains a 19 character string. E.g. 002559-002677-AN-01 or 002334-100385-AN-01

I need to change the 8th character and depending on what the original value is replace it with a different value, e.g 002559-002677-AN-01 the 8th character in this string is "0" and therefore needs to be "2" whereas in this string 002334-100385-AN-01, the 8th character is "2" and needs to be "4".

I have tried the following script:

Update "drawing"
set "contract"= replace(contract,SUBSTRING(contract,8,1),'2')
where "fieldx3" ='2bupdated'

I know that this doesn't define the character value that needs replacing e.g. from the first example above "0", only (I believed) the position, but when I run it it changes all the/any "0"s in the string to "2"

Where am I going wrong and how can I specify the value to be replaced.

I don't really work with, or know sql, just occasionally I get landed with these types of job!

Any help would be fantastic, thankyou
Sep 24 '09 #1
Share this Question
Share on Google+
2 Replies

P: 3
Please, I apologise if I've offended anyone...! Please can someone help with this.

I've figured a way to eliminate the need to specify the defining value but I still can't get the substring function to work so that the change only affects the 8th character in the string??

Can someone please share their expertise with me, many thanks
Sep 24 '09 #2

P: 293

U can implement this by using case when statement.

use case when SUBSTRING(contract,8,1) = 0 the '2'
when SUBSTRING(contract,8,1) = 2 the '4' likewise..

Sep 29 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.