Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Portland, Oregon
    Posts
    5

    Cool Unanswered: Working with substrings

    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

  2. #2
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100

    Re: Working with substrings

    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
    Last edited by TimS; 11-22-03 at 02:00.

  3. #3
    Join Date
    Nov 2003
    Location
    Portland, Oregon
    Posts
    5

    Thanks!!

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

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    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 ?

  5. #5
    Join Date
    Nov 2003
    Location
    Portland, Oregon
    Posts
    5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •