Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Posts
    5

    Unanswered: Split a single column data in to 2 columns

    Hi
    This is probably a very basic question for most people in this group.
    How do i split the data in a column in to 2 columns? This can be done in access with an update query but in MS SQL server I am not sure.
    Here is an example of what i want to acheive

    FName
    John?Doe

    FName LName
    John Doe

    thanks for the help
    prit

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use something like:
    Code:
    UPDATE myTable
       SET
          fname = Left(fname, CharIndex('?', fname) - 1)
    ,     lname = SubString(fname, CharIndex('?', fname), 3999)
       WHERE  fname LIKE '%?%'
    -PatP

  3. #3
    Join Date
    Jan 2005
    Posts
    5
    Thanks Pat
    When i use the code i get an error

    Server: Msg 8114, Level 16, State 5, Procedure Part_Trigger, Line 26
    Error converting data type varchar to numeric.

    I get the same error when I use the Right command inplace of the SubString without the last argument since it takes 2 arguments.

    Any ideas?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first guess would be that something is wrong with the code

    If you post a copy of your trigger, I'd be able to offer a better informed comment on it.

    -PatP

  5. #5
    Join Date
    Jan 2005
    Posts
    5
    Quote Originally Posted by Pat Phelan
    My first guess would be that something is wrong with the code

    If you post a copy of your trigger, I'd be able to offer a better informed comment on it.

    -PatP
    Here is the code for the trigger

    CREATE TRIGGER Part_Trigger ON dbo.tblParticipant
    FOR INSERT, UPDATE
    AS

    BEGIN
    DECLARE @transaction_type varchar(6)


    If Exists(Select * from deleted) -- updated record write before and after images to the transaction table
    begin
    INSERT INTO LogX_tblparticipant (Planid, SSN, title, fname, mname, lname, transaction_type, process_status)
    SELECT i.planid, i.SSN, i.title, i.fname, i.mname, i.lname, i.cumulativehours, 'After', 'P'

    FROM inserted i

    INSERT INTO LogX_tblparticipant (Planid, SSN, title, fname, mname, lname, transaction_type, process_status)
    SELECT d.planid, d.SSN, d.title, d.fname, d.mname, d.lname, 'Before', 'P'

    FROM deleted d

    end

    else
    begin

    INSERT INTO LogX_tblparticipant (Planid, SSN, title, fname, mname, lname, transaction_type, process_status)

    SELECT i.planid, i.SSN, i.title, i.fname, i.mname, i.lname, 'Insert','P'
    FROM inserted i

    end


    end


    RETURN
    Last edited by prithwish; 01-14-05 at 11:40.

  6. #6
    Join Date
    Jan 2005
    Posts
    5
    thanks a lot Pat
    The problem was with the trigger and some of the fileds it was updating in another table. Now I know to how to look for the trigger and other table dependencies.
    Your Code worked perfect. Now I labor with another problem.
    I wanted a single character before the '.'
    Will send a post if i can't get this working by the end of the day.
    thanks a lot

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, this shouldn't work at all:

    INSERT INTO LogX_tblparticipant (Planid, SSN, title, fname, mname, lname, transaction_type, process_status)
    SELECT i.planid, i.SSN, i.title, i.fname, i.mname, i.lname, i.cumulativehours, 'After', 'P'

    You are trying to insert 9 columns into 8 fields.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jan 2005
    Posts
    5
    sorry my mistake there was another field called cumulativehours in the insert statement.
    thanks for pointing it out

Posting Permissions

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