June 26, 2012 at 3:21 pm
I have a table with a field in it called branchcodes.
My problem is the branchcodes are wrong, but only the first three characters are wrong.
So, I need to update the branchcodes, but only change the first three characters, leaving the trailing three characters.
How do I do this?
June 26, 2012 at 3:22 pm
Depends on what's wrong and how they need to be changed. Just from your vague description, not a clue on how I would proceed.
June 26, 2012 at 3:28 pm
krypto69 (6/26/2012)
I have a table with a field in it called branchcodes.My problem is the branchcodes are wrong, but only the first three characters are wrong.
So, I need to update the branchcodes, but only change the first three characters, leaving the trailing three characters.
How do I do this?
Lookup "STUFF" in BOL. It's a function that works very well for this.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2012 at 3:27 pm
Exactly what I needed.
Thanks Jeff.
June 28, 2012 at 8:10 am
you can probably achieve what you need with a combination of CASE, LEFT and SUBSTRING. but it depends on the details of your requirement. it might be as simple as
UPDATE tblFoo
SET cFoo1 = 'ABC' + SUBSTRING(cFoo1,4,3)
WHERE LEFT(cFoo1,3) = 'ACB'
but if the first 3 letters need to be different depending on the current 3 letters it will be more complex eg
UPDATE tblFoo
SET cFoo1 = CASE LEFT(cFoo1,3)
WHEN 'ACB' THEN 'ABC' + SUBSTRING(cFoo1,4,3)
WHEN 'DFE' THEN 'DEF' + SUBSTRING(cFoo1,4,3)
END
WHERE LEFT(cFoo1,3) NOT IN ('ABC','DEF')
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
June 28, 2012 at 8:24 am
It al depends on what your changing these values to, are your changes to the first 3 characters going to be the same for every row? if so then perhaps something like this
update table set column= replace(column,left(column,3),'')
***The first step is always the hardest *******
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy