SQL Server update command question

Question

i'm using sql server 2000 and i have some table need to update.

one of problem is how to update some portion of column?

for example: here is two table one is OrderDate and ShipName and

i want to change Date value in OrderDate, such like all 1996 year to 1999 year and

all 'tom' in ShipName to 'ken' value

OrderDate    ShipName                          
1996-07-04   Vins et alcools tom         
1996-07-05   Toms Spezialitaten                
1996-07-08   Hanari tom
1996-07-08   Victuailles en tom             
1996-07-09   Supremes tom                  
1996-07-10   Hanari tom

so what i want result will be

OrderDate    ShipName                          
1999-07-04   Vins et alcools ken         
1999-07-05   Toms Spezialitaten                
1999-07-08   Hanari ken
1999-07-08   Victuailles en ken             
1999-07-09   Supremes ken            
1999-07-10   Hanari ken

thanks in advance. if anyone help me much appreciate!

Answer 1

Just using the SQL Server replace function on tom would result in the following:

Correct

'something tom' becomes 'something ken'

Incorrect

'Toms something' becomes 'kens something'

To do that sort of string replacement (only replacing tom with ken, not toms with kens) you would need a regular expression.

SQL Server does not support regular expressions in the t-sql language.

In SQL Server 2005 and above you can integrate regular expressions by using the clr integration but I'd personally say that is a very bad idea. For these sort of updates it is probably cleanest to do the string manipulation work in a more expressive language and then submit the updates after making you changes.

Looking at your example data, there is one (albeit risky) case where you can achieve what you want. If the string tom is always at the end, you can do an update like this:

update TableName
set ShipName = substring(ShipName, 0, len(ShipName) -3) + ' ken'
where ShipName like '% tom'

That will replace all instances of tom at the end with ken.

For the date part of your question you can do something like the following:

Update TableName
set OrderDate = dateadd(year, 3, OrderDate)
where datepart(year, OrderDate) = 1996

Answer 2

To update the order date, try something like update table set orderdate = dateadd(year, 3, orderdate)

to update the shipname you could try update table set shipname = replace(shipname, "tom", "ken")

Answer 3

Try using the REPLACE keyword: http://msdn.microsoft.com/en-us/library/ms186862.aspx The SUBSTRING keyword may help you as well: http://msdn.microsoft.com/en-us/library/aa259342%28SQL.80%29.aspx

Per your ship example:

UPDATE Table1

SET ShipName = REPLACE(table1.ShipName,tom, ken)

WHERE Some_Column = Some_Value

Hope this will at least get you started.