Deleting all characters to the right of char(13) or char(10).

• gcook 48147

  SSC Veteran

  Points: 222

  More actions

  September 2, 2014 at 4:51 pm

  #150177

  I have a table that has a varchar(255) column. In that column, there are instances where data has a carriage return or line feed.

  I would like to delete all text to the right of the carriage return or line feed as well as the carriage return and line feed itself. This column is exposed as an open text field in the application so there is no standardized data lengths or standard for the data.

  So, for instance, I would like:

  INTERNAL COMMUNICATIONS:{Char(13)}Blah blah blah blah

  to be only:

  INTERNAL COMMUNICATIONS:

  and

  NOTES/MEMOS:{Char(13)}This is a random note. It could go on for up to 200+ charaters, etc.

  to be only:

  NOTES/MEMOS:

  😀

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 2, 2014 at 5:04 pm

  #1742643

  Give this a try:

  with basedata as ( -- creating test data

  select

  *

  from

  (values ('INTERNAL COMMUNICATIONS:' + Char(13) + 'Blah blah blah blah'),

  ('NOTES/MEMOS:' + Char(13) + 'This is a random note. It could go on for up to 200+ charaters, etc.'),

  ('INTERNAL COMMUNICATIONS:' + Char(10) + 'Blah blah blah blah'),

  ('NOTES/MEMOS:' + Char(10) + 'This is a random note. It could go on for up to 200+ charaters, etc.'))dt(TestStr)

  )

  select

  *,

  left(TestStr,patindex('%[' + char(13) + char(10) + ']%', TestStr) - 1)

  from

  basedata