Update part of a string in database entries

[mgason]

Hi,
I not a SQL expert, I need to write a query to make these changes...

I have Batch ID's stored in a table.
They have 3 parts separated by colons Recipe:Revision:Unique ID.
I want to update the records with a paticular Recipe and change them to a new recipe name. (changing real customer data for a generic demo application)
I do not know how many characters the Recipe name is but it is always followed by the first colon.

How can I say

Update records from Mixed_Material_Weighments table, BatchID field where characters before first colon = "my old recipe name" replace those characters with "my new recipe name"

As some Recipe names may have common beginnigs I don't want to just say where first 3 characters = AW1, that may be a Recipe name but there could also be one called AW1C
thanks
mark

 

[dGrouse]

The function you're looking for is "charindex", which returns the location of the first instance of a specified character:

update mixed_material_weighments
set recipename = [new name]
where left(batchID,charindex(':',batchID)) = [recipe code in question]

Btw, I encourage you to decompose your 3-part batchID key into three separate columns (and declare them as the primary key, as appropriate). "Intelligent" keys like that are usually a pina. Let us know if you need help doing that!

Cheers -



Jeff Prenevost
BI Consultant
Ann Arbor, MI

 

[RiverGuy]

Here's an example. Run this, then modify to use against your table as needed.

CREATE TABLE #Temp1
(Col1 VARCHAR(100)
)

INSERT INTO #Temp1 SELECT 'Recipe#1:3:100'

SELECT * FROM #Temp1

UPDATE #Temp1 SET Col1 = 'New Recipe Name' + SUBSTRING(Col1, CHARINDEX(':', Col1), LEN(Col1)-CHARINDEX(':', Col1))

SELECT * FROM #Temp1

DROP TABLE #Temp1

 

[dGrouse]

Whoops! Posted that a little too fast. You need to take one less than the X left most characters, where "X" is the location of the first colon:

update mixed_material_weighments
set recipename = [new name]
where left(batchID,charindex(':',batchID) - 1) = [recipe code in question]

Jeff Prenevost
BI Consultant
Ann Arbor, MI