• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

SQL Server Help with Update Statement

Hi.. I need to clean up data in a LASTNAME field.
I need to find and replace all the 'ii' and replace with 'II' and find all the 'iii' and replace with 'III'



The data looks like this

jones  ii
smith iii
brown
green ii


Need it look like this
Jones II
Smith III
Brown
Green II


Basically capitalize the '2nd' and '3rd'
0
JElster
Asked:
JElster
2 Solutions
 
Steve WalesSenior Database AdministratorCommented:
Check the Replace Function: http://technet.microsoft.com/en-us/library/ms186862.aspx

The space before the string to replace should make sure it picks it up at the end of the string, incase there's a last name that has ii or iii as a part of the actual name and not the suffix.

update table1
set lastname = replace(lastname, ' iii', ' III');

update table1
set lastname = replace(lastname, ' ii', ' II');

Open in new window


You can check what the output of the update would look like before the update by doing:

select lastname, replace(lastname, ' ii', ' II') from table1;

Open in new window

0
 
John_VidmarCommented:
UPDATE	yourtable
SET	lastname = replace(replace(lastname, ' iii', ' III'), ' ii', ' II')
WHERE	lastname like '%ii%'

Open in new window

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now