Want to Remove or Replace the Carriage Return

• nikishh9

  Old Hand

  Points: 312

  More actions

  June 23, 2011 at 9:35 am

  #98210

  Hi All,

  I load one flat file to sql server. in that table there is one fileld Extended_Desc column. That column contain Carriage Return when i fire this query 'select ASCII(extended_desc) from table' its return 13.

  Now i want to replace it into NULL value. i dont that one is a right option or not. if you have some suggetion then Please give me.

  so how i replace to that Carriage Return to NULL value?

  Thank you in Advance for any Help.

  For the reference i attached the file. Please have look.

• Sean Lange

  SSC Guru

  Points: 286609

  More actions

  June 24, 2011 at 7:22 am

  #1343646

  Sure you can replace it with null or use the nullif function.

  declare @Return varchar(5)

  set @Return = CHAR(13)

  select REPLACE(@return, char(13), null), nullif(@Return, char(13))

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• nikishh9

  Old Hand

  Points: 312

  More actions

  June 24, 2011 at 2:49 pm

  #1343931

  Thank you very much

  i appreciate for your Great Help..