Updating first three characters

Question

Updating first three characters

krypto69

SSChampion

Points: 13591
More actions
June 26, 2012 at 3:21 pm

#276852

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?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

krypto69 SSChampion Points: 13591 More actions · Answer 3

krypto69

SSChampion

Points: 13591

June 27, 2012 at 3:27 pm

#1506380

Exactly what I needed.

Thanks Jeff.

BenWard SSCertifiable Points: 5903 More actions · Answer 4

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
----------------------------------------

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 5

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 *******