CODEWITHSUNDEEP
sqlsql-server-2008
ncoder
ncoder

Reputation: 165

Replacing String in SQL

I have Database table and I am using SQL Server 2008.

The table contins some columns and in one column the variable type is "String." The column looks like as follows:

ColumnA

12301 01

12342 02

12501 01

43201 02

56401 03

56766 01

53478 01

54601 04

There is a white space in between the first 5 digits and last two digits. I want to replace last two digits to 09 if last two digits in the string are 01.

Upvotes: 2

Views: 500

Answers (3)

James Hill
James Hill

Reputation: 61793

I would use the LIKE operator to find all rows that end in a [space] + 01 and then use the REPLACE method to replace 01 with 09

UPDATE  [YourTable]
SET     ColumnA = REPLACE(ColumnA, ' 01', ' 09')
WHERE   ColumnA LIKE '% 01'

You can test the query here: https://data.stackexchange.com/stackoverflow/q/116590/

WARNING:

Never run updates statements against your production data without first testing them in a development environment - especially if you didn't write the SQL!

Upvotes: 3

sll
sll

Reputation: 62484

Since column values always has single space before last two characters you can leverage this to simplify replacement logic, so you do not need any complex checks:

UPDATE Table
SET ColumnA = REPLACE (ColumnA, ' 01', '09')
WHERE ColumnA LIKE '%' + ' 01'

Upvotes: 1

John Hartsock
John Hartsock

Reputation: 86862

UPDATE Table
  SET ColumnName = LEFT(ColumName, 6) + '09'
Where ColumnName LIKE '[0-9][0-9][0-9][0-9][0-9] 01'

Upvotes: 0

Related Questions