+ Reply to Thread
Results 1 to 2 of 2

Remove Comma from a Text Field

  1. Remove Comma from a Text Field

    Can anyone tell me how to remove a comma ',' from a text field? I am coping
    records to a csv and it is separating my text field into two columns when it
    finally reaches the CSV. SQL Server 2000 SP4. Txs.

    Select notes
    from rn_appointments
    results:
    Col 1 Col2
    How do you --do

    returns:
    Server: Msg 279, Level 16, State 2, Line 1
    The text, ntext, and image data types are invalid in this subquery or
    aggregate expression.
    Server: Msg 403, Level 16, State 1, Line 1
    Invalid operator for data type. Operator equals add, type equals text.

    I tried:
    UPDATE rn_appointments
    SET notes = REPLACE(notes, ',', '')

    and got this error:
    Server: Msg 8116, Level 16, State 1, Line 1
    Argument data type text is invalid for argument 1 of replace function.


    I tried : SELECT MAX(col1)+','+MIN(col1)
    FROM some_table ;
    and got this error:




    Ray

  2. Re: Remove Comma from a Text Field

    Ray,

    It was hard to figure out if you really need to remove the comma or
    just have it correctly read by your CSV file. Many CSV applications get
    around this problem by using enclosing quotes around fields that contain a
    comma. If that is the case with your application try the following:

    select '"' + notes + '"' as notes from rn_appointments.

    If it is not clear I am wrapping the notes field in double quotes for
    the CSV file and wrapping the double quotes with single quotes for the SQL
    parser.

    Hope this helps,

    Mark



    "Ray" wrote in message
    news:4B583E2A-2F6D-47CE-95E0-85481205437B@microsoft.com...
    > Can anyone tell me how to remove a comma ',' from a text field? I am
    > coping
    > records to a csv and it is separating my text field into two columns when
    > it
    > finally reaches the CSV. SQL Server 2000 SP4. Txs.
    >
    > Select notes
    > from rn_appointments
    > results:
    > Col 1 Col2
    > How do you --do
    >
    > returns:
    > Server: Msg 279, Level 16, State 2, Line 1
    > The text, ntext, and image data types are invalid in this subquery or
    > aggregate expression.
    > Server: Msg 403, Level 16, State 1, Line 1
    > Invalid operator for data type. Operator equals add, type equals text.
    >
    > I tried:
    > UPDATE rn_appointments
    > SET notes = REPLACE(notes, ',', '')
    >
    > and got this error:
    > Server: Msg 8116, Level 16, State 1, Line 1
    > Argument data type text is invalid for argument 1 of replace function.
    >
    >
    > I tried : SELECT MAX(col1)+','+MIN(col1)
    > FROM some_table ;
    > and got this error:
    >
    >
    >
    >
    > Ray




+ Reply to Thread