Reputation: 11

SQL Server Update Quetion

I have a little question about SQL Server 2008 and have a column for a game

The column is for example:

xx984916516518
xx191981981989

where

xx = 45 (for example)

Is there any way that I can update the 45 to 30 (example)

And if this is possible, can I do the same with this:

xxyy1581zz9878

xx = 45
yy = 45
zz = 45

I also want just update the 45.

I could make it with:

UPDATE Table
SET Column = '30301581309878'
WHERE Column = '45451581459878'

But I have over 10.000 of this type, this would take too much time.

Upvotes: 1

Answers (2)

Reputation: 69504

Example with Replace Function

DECLARE @String VARCHAR(100) = 'xx9849165zz16518yy'

SELECT REPLACE(REPLACE(REPLACE(@String, 'xx', '45'), 'zz', '45') , 'yy', '45')

Result

459849165451651845

If you want to update a column in your table you could do something like this

UPDATE Table_Name

SET Column_Name = REPLACE(REPLACE(REPLACE(Column_Name, 'xx', '45'), 'zz', '45') , 'yy', '45')

To Replace '30' with '45'

UPDATE Table_Name 

SET Column_Name = REPLACE(Column_Name, '30', '45')

Upvotes: 1

Reputation: 6999

UPDATE YourTable
SET SomeColumn = '30' + substring(SomeColumn, 3, LEN(SomeColumn - 2))
WHERE SomeColumn like '45%'

Upvotes: 2