Replace substring of field

[mhaff]

MS SQL Server 8.00.194

I have a field called "ComponentTag" in a table called "Component", below is an example of values:

001-001
001-002
001-003

I need to replace the beginning '001' with '010' in the
records that start with '001' so result would be

010-001
010-002
010-003

I'm fairly new to SQL so I appreciate any assistance.

 

[swampBoogie]

update component
  set componentTag = '010' || substring(componentTag from 4)
 where componentTag like '001%'

 

[mhaff]

Thank you. However, when I execute that statement I get the error: "Incorrect syntax near '|'"

One thing I should've mentioned, I'm executing the statement
through the database application. However the application runs on MS SQL server. I don't know if this is a problem or not.

 

[swampBoogie]

Well, as SQL server is not compliant with the SQL standard it does matter.

update component
  set componentTag = '010' + substring(componentTag,4,len(componentTag)-3)
 where componentTag like '001%'

 

[rwheels]

Here's how I would write it.

Update component
set ComponentTag = ('010' + substring(ComponentTag,4,4))
where substring(ComponentTag,1,3) = '001'


--this is assuming your ComponentTag field is only 7 characters long.

Hope this helps...