Update a column but only part of the text

Question

Post reply

Update a column but only part of the text

TRACEY-320982

SSChampion

Points: 13653
More actions
April 8, 2010 at 2:56 pm

#221121

DECLARE @MYSTRING NVARCHAR(100)
SET @MYSTRING = 'MY VALUE 000 CHANGE'
I need to update a value in a database from
'MY VALUE 000 CHANGE'
'MY VALUE A1000 CHANGE'
What function should i use the charindex
UPDATE TABLE
SET COLUMN = @MYSTRING (Needs to have 'MY VALUE A1000') CHANGE)
WHERE COLUMN = @MYSTRING (Need to change 'MY VALUE 000 CHANGE' to 'MY VALUE A1000 CHANGE'
The values would be given to be as change
00000 to A10000
00001 to C10000
I would not know what position of the text it is..could be postion 14 or could be 70.
Any ideas

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

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

K Cline Ten Centuries Points: 1391 More actions · Answer 1

Update Table

Set Column = REPLACE(Column, '00000', 'A10000')

Where (CHARINDEX('00000', Column, 1) > 0);

Update Table

Set Column = REPLACE(Column, '00001', 'C10000')

Where (CHARINDEX('00001', Column, 1) > 0);

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 2

Worked a treat and replaced it anywhere it found it in the string.

Thank you so much got a hugh update to do.

What does the 1 mean ?

(CHARINDEX('00001', column, 1)

WayneS SSC Guru Points: 95460 More actions · Answer 3

TRACEY-320982 (4/8/2010)
Worked a treat and replaced it anywhere it found it in the string.
Thank you so much got a hugh update to do.
What does the 1 mean ?
(CHARINDEX('00001', column, 1)

http://www.lmgtfy.com/?q=charindex+sql+server+2005

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

TRACEY-320982 (4/8/2010)
Worked a treat and replaced it anywhere it found it in the string.
Thank you so much got a hugh update to do.
What does the 1 mean ?
(CHARINDEX('00001', column, 1)

My recommendation is that you read about it in Books Online (the help system that comes with SQL Server) because the operand where the "1" is is very useful.

My other recommendation is that you read about all of the functions in SQL Server because, as with any other language, more than half the ability to solve a problem in a given languge comes from its functions.

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