SQL Server Performance

BCP problem with carriage return

Discussion in 'General DBA Questions' started by sriraj, Aug 8, 2007.

  1. sriraj New Member

    I am trying to BCP out data from a particular table into a text file . But In one of the columns [varchar (390)] which is used to enter coments in the front end, there are sevaral rows which have carriage return.
    Since the default row delimiter is carriage return, the sql server is treating the fields after the carriage return as new rows.
    Is there a work around to this probelm ??
    below is the BCP i am using.
    BCP databasename..tablename out c: ablename.txt -c -t"|" -r
    -Usa -Ppassword -S servername
    Any help highly appreciated.
  2. ndinakar Member

    I would write a VIEW for that and use the view to BCP OUT the data. In the view, use a replace.
    CREATE VIEW dbo.vwSomeView AS
    SELECT col1, col2, replace(replace(Column3,char(10),space(1)),char(13),space(1)) as Column3, column4
    FROM dbo.SomeTable
    And do a SELECT * FROM dbo.vwSomeView instead of table name in your BCP command.
  3. Jon M Member

    Another solution is by creating a User Defined Function. Your function will have an input param and will look for the pattern of CHAR(13) from the input param and delete them. The function is somewhat similar to this:
    CREATE FUNCTION [dbo].[del_EnterKeys] (@i_param as varchar(390))
    RETURNS varchar(390)
    if patindex('%'+char(13)+'%',@i_param) > 0
    set @i_param = substring(@i_param,1,patindex('%'+char(13)+'%',@i_param)-1)+substring(@i_param,patindex('%'+char(13)+'%',@i_param)+2,390)
    return @i_param
    Then you may now call your BCP as:
    BCP "select mydb.dbo.del_EnterKeys(columnwithEnter),column2,column3...and so on" queryout c: ablename.txt -c -t"|" -r
    -Usa -Ppassword
    Jon M

Share This Page