I need to write a T-SQL script that updates only the title in a row.

For examples, I need to update Mr Test Test to be Mr. Test Test where the Mr is changed to Mr. with a .

I need to do this for few titles though.

How can I do this?

CodePudding user response：

I assume the titles are only at the beginning of the string and they are always seperated by a space.

Create test environment

USE tempdb;

-- Create the test table
CREATE TABLE dbo.Test
(
    ID     int           NOT NULL PRIMARY KEY IDENTITY(1,1),
    [Name] nvarchar(100) NOT NULL
);

-- Insert test data
INSERT INTO dbo.Test ( [Name] )
VALUES
( N'Mr Edison' ), 
( N'Mrs Rosenbaum' ), 
( N'Lt McAllister' ), 
( N'Mr. White' ), 
( N'Smith' ), 
( N'John Meyer' ), 
( N'Mr Mrson' );

Update rows

UPDATE dbo.Test 
SET [Name] =
    LEFT([Name], CHARINDEX(N' ', [Name]) - 1)  
    N'. '  
    SUBSTRING([Name], CHARINDEX(N' ', [Name])   1, 99)
WHERE CHARINDEX(N' ', [Name]) > 0
  AND LEFT([Name], CHARINDEX(N' ', [Name]) - 1) IN
  (
    N'Mr',
    N'Mrs',
    N'Lt'
  );

Resulting rows

Cleanup

DROP TABLE dbo.Test

Hope that helps!

If this post answers your question please mark it as answer, thanks!

CodePudding user response：

What @Andres posted should do the trick. I am a fan of storing prefixes in a table so that you don't need to update your code whenever you want to address a new one. Borrowing Andres' code I put this together...

First the sample data and sample pattern table:

-- Create the test table
DECLARE @Test TABLE 
(
    ID     int           NOT NULL PRIMARY KEY IDENTITY(1,1),
    [Name] nvarchar(100) NOT NULL
);

-- Create the prefix table
DECLARE @prefix TABLE
(
    ID     int           NOT NULL PRIMARY KEY IDENTITY(1,1),
    Prefix nvarchar(100) NOT NULL
);

-- Insert test data
INSERT @Test([Name]) VALUES ( N'Mr Edison' ), ( N'Mrs Rosenbaum' ), ( N'Lt McAllister' ),
                        ( N'Mr. White' ),( N'Smith' ), ( N'John Meyer' ), ( N'Mr Mrson' ),
                        ( N'Dr Jones' ),( N'Dr. Smith' ), ( N'Mrs John Meyer' ),('Ms B');

INSERT @prefix(Prefix) VALUES('Mr'),('Ms'),('Mrs'),('Lt'),('Dr');

Next a query that shows what we're about to do:

-- Select statement that shows what we are doing
SELECT 
  t.Id,
  OldName   = t.[Name],
  [NewName] =  STUFF(t.[Name], CHARINDEX(p.Prefix ' ',t.[name]) LEN(p.Prefix), 0,'.')
FROM @Test   AS t
JOIN @prefix AS p ON CHARINDEX(p.Prefix ' ',t.[name])>0;

Results:

Id   OldName         NewName
---- --------------- ---------------
1    Mr Edison       Mr. Edison
7    Mr Mrson        Mr. Mrson
11   Ms B            Ms. B
2    Mrs Rosenbaum   Mrs. Rosenbaum
10   Mrs John Meye   Mrs. John Meyer
3    Lt McAllister   Lt. McAllister
8    Dr Jones        Dr. Jones

The UPDATE statement:

UPDATE @Test
SET [Name] = STUFF(t.[Name], CHARINDEX(p.Prefix ' ',t.[name]) LEN(p.Prefix), 0,'.')
FROM @Test   AS t
JOIN @prefix AS p ON CHARINDEX(p.Prefix ' ',t.[name])>0;

Results:

ID    Name
----- --------------------
1     Mr. Edison
2     Mrs. Rosenbaum
3     Lt. McAllister
4     Mr. White
5     Smith
6     John Meyer
7     Mr. Mrson
8     Dr. Jones
9     Dr. Smith
10    Mrs. John Meyer
11    Ms. B

Note that I search for the prefix followed by a space so as to avoid change "Mr. Wes Simson" to "Mr. Wes Sims.son"