asked on

Need to INSERT Apostrophes to Name values in SQL Server 2014 SP2 table

Hi,
I have a table with a surname column, a sample of which is below.

O REGAN
O SULLIVAN
O MAHONY
O SULLIVAN

I need to update all the names like the values below with an apostrophe (see below)

O’ REGAN
O’ SULLIVAN
O’MAHONY
O’ SULLIVAN

I can find all the relevant values needing updating using

WHERE LTRIM(em_employee_surname) like 'O %'

Open in new window

I need some guidance re the UPDATE statement
Thank you

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

blossompark

ASKER

thanks Ryan, will try and update you later

ste5an

When your predicate

WHERE LTRIM(em_employee_surname) LIKE 'O %'

Open in new window

is really sufficient to identify only the affected rows, then it is a simple substring replace, cause it means that leading spaces should be imho also stripped:

UPDATE yourTable
SET em_employee_surname = 'O''' + LTRIM(SUBSTRING(LTRIM(em_employee_surname)), 3, 1024))
WHERE LTRIM(em_employee_surname) LIKE 'O %';

Open in new window

Caveat: Ensure that you have a working backup.

blossompark

ASKER

Hi ste5an,
thanks for your input.
why did you set the Length argument to 1024?

ste5an

Just set the length to the maximum length of your column definition. 1024 was just a well-known, hopefully large enough value.

slightwv (䄆 Netminder)

I agree with suggestions to make a backup first!

To me this seems easier:

UPDATE yourTable
SET em_employee_surname = stuff(em_employee_surname,charindex(' ',em_employee_surname),1,'''')
WHERE LTRIM(em_employee_surname) LIKE 'O %';

Open in new window

blossompark

ASKER

Thanks Ryan, ste5an and slightwv, i have plenty of options now thanks ;-)