CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Deepan Raj
Deepan Raj

Reputation: 314

How to use Like Operator during update

I have these type of docnos (BP/2016/00344,BP/2016/00345,BP/2016/00346) and (CP/2016/00344,CP/2016/00345,CP/2016/00346)

like that i have more than 100 records,now my task is to change the year to 2015 instead of 2016 by one update query

pls help me

Upvotes: 0

Views: 54

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

This is an elaboration on xQbert's approach. Assuming that the year always starts on 4th character, then you can do:

update t
    set docno = stuff(docno, 7, 1, '6')
    where docno like '__/2015/%';

Upvotes: 3

Doolius
Doolius

Reputation: 874

UPDATE Table
SET docnos = REPLACE(docnos,'/2015/','/2016/')

This should work as well.

Also, like xQbert said, if LIKE is a must use you can add in where docnos like '%/2015/%'

Upvotes: 3

xQbert
xQbert

Reputation: 35333

Why do you need to use a like? I would think like could result in improper records being updated.

It appears year is always in positions 4-7. If this is true, using string functions this task becomes pretty straight forward.

I suggest the following approach over using a replace function because your trailing numbers in your string of 00345 may reach 2015 and I don't think you'd want to replace them.

So UNTESTED:

update table 
set docnos = left(Docnos,3)+'2016'+right(Docnos,len(docnos)-7)
where substring(docnos,4,4) = '2015'
  • Why minus 7? (3 for ZZ/, 4 more for the year totaling 7)
  • Why use a where clause? becuase you indicated you only wanted to update the 2015 records.

Assumptions.

  1. First 3 positions will ALWAYS Be in format ZZ/ where zz could be any 2 values
  2. Positions 4-7 are the year you need to evaluate

Now if you must use a like...

I Suppose we could change our where to be where docNo like '%/2015/%'

Upvotes: 3

Related Questions