Hi guys!!

I am an SQL newbie and would like info on working with substrings in SQL (I am using SQL2000).

Here is a sample of the data I am working with:

EmployeeID LastName FirstName
----------- -------- -------------------------
1 (CLOSED) Nancy Davolio (ACAT)
2 (CLOSED) Andrew Fuller (ACAT)
3 (CLOSED) Janet Leverling (ACAT)
4 (CLOSED) Margaret Peacock (ACAT)
5 (CLOSED) Steven Buchanan (ACAT)
6 (CLOSED) Michael Suyama (ACAT)
7 (CLOSED) Robert King (ACAT)
8 (CLOSED) Laura Callahan (ACAT)
9 (CLOSED) Anne Dodsworth (ACAT)

I need a script that will do the following on all records:

1. delete (ACAT) from the end of the First name
2. replace (CLOSED) with the proper last name

For example for the first record it should look like this when the script is done:

EmployeeID LastName FirstName
----------- -------- -------------------------
1 Davolio Nancy


Any ideas? I am new to SQL and need some help in getting me started. Also, any links to reference material would be great!!

Thanks!!

Sam

It helps if you post a create table and insert statements

My guess below.

Note: how I am using a commented out select to test my update code. In Query Window I high light right after the two dashes and run the highlighted code.

I use the BOL (Books on line) for looking up syntax on this type of problem.

Tim S


CREATE TABLE test5 (EmployeeID INT NOT NULL PRIMARY KEY, LastName VARCHAR(32), FirstName VARCHAR(64))

INSERT test5 (EmployeeID, LastName, FirstName) VALUES (1, '(CLOSED)', 'Nancy Davolio (ACAT)')
INSERT test5 (EmployeeID, LastName, FirstName) VALUES (7, '(CLOSED)', 'Robert King (ACAT)')

-- remove (ACAT)
UPDATE t SET FirstName = RTRIM(SUBSTRING(FirstName, 1, CHARINDEX('(ACAT)', FirstName) -1 ))
-- SELECT FirstName, RTRIM(SUBSTRING(FirstName, 1, CHARINDEX('(ACAT)', FirstName) -1 ))
FROM test5 t
WHERE FirstName LIKE '%(ACAT)'

-- update last name
UPDATE t SET LastName = LTRIM(SUBSTRING(FirstName, (LEN(FirstName) - CHARINDEX(' ', REVERSE ( FirstName)) + 1 ), CHARINDEX(' ', REVERSE ( FirstName)) ))
-- SELECT FirstName, LTRIM(SUBSTRING(FirstName, (LEN(FirstName) - CHARINDEX(' ', REVERSE ( FirstName)) + 1 ), CHARINDEX(' ', REVERSE ( FirstName)) ))
FROM test5 t
WHERE LastName = '(CLOSED)'

SELECT * FROM test5

EmployeeID LastName FirstName
----------- -------------------------------- ----------------
1 Davolio Nancy Davolio
7 King Robert King

thanks Tim!! I'll give it a shot and let you know how it goes.

Look at the replace function. Is this a text file you are importing ? Or existing sql data (if existing what is the actual format of the data you are showing - is it one string?) ? Are there delimiters ?

It is existing SQL data... It isn't one string. The sample data I provided did not appear as a table as I intended. The EmployeeID is the number, LastName is (CLOSED) and the rest is the FirstName. I hope that makes sense.

TimS' script worked great. I had to tweak it a little though but it was essentially what I was looking for.

Thanks everyone!!

Sam