Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deleting all characters to the right of char(13) or char(10). Expand / Collapse
Author
Message
Posted Tuesday, September 2, 2014 4:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:27 PM
Points: 4, Visits: 9
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:

Post #1609843
Posted Tuesday, September 2, 2014 5:04 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:23 PM
Points: 20,860, Visits: 32,883
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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1609845
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse