Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Being able to replace characters in a string of text can be useful in solving a variety of problems. Thankfully SQL Server provides a REPLACE command which allows for sections of a string to be replaced with another set of characters.

Here is a simple example to demonstrate how REPLACE works:-

 SELECT REPLACE('My name is Fred', 'Fred', 'Frank')

Replace looks at the text string and replaces any instances of ‘Fred’ with ‘Frank’. Therefore, the output of the query will read:-

My name is Frank

When used in conjunction with an UPDATE command, we can use REPLACE to alter characters within a column. In this example we are changing the monthly salary of all staff from British pounds to US dollars. We are not converting the salaries, but simply changing the ‘£’ to a ‘$’, so in fact everyone is receiving a pay cut!

UPDATE salary_table
SET monthly_salary = REPLACE(monthly_salary, '£', '$')

Instead of applying the change to all members of staff, what we should do is use a WHERE clause to ensure the REPLACE only affects those staff who should be getting paid in dollars:-

UPDATE salary_table
SET monthly_salary = REPLACE(monthly_Salary, '£', '$')
WHERE nationality = 'American'

If we check the salary_table now, then we will find that records such as,

Bob Jones            American             £2,500

will now read as follows,

Bob Jones            American             $2,500


Tags: , , , , ,

%d bloggers like this: