Finding Carriage Returns & Line breaks in SQL Strings/Text
June 5, 2008A short one today.
If you need to find the first carriage return in a field (char/varchar/nchar/nvarchar/binary/varbinary data types only) you can use the following:
–====================================================
Select
CarriageRTN_position = CHARINDEX(CHAR(13) + CHAR(10), yourcolumn)
From
yourtable
–====================================================
If you need to find the first carriage return in a field (text data type) you can use the following:
–====================================================
Select
CarriageRTN_position = PATINDEX(‘%’ + CHAR(13) + CHAR(10) + ‘%’, yourcolumn)
From
yourtable
–====================================================
Once you have the position of the carriage return you can extract the string you need using the SUBSTRING() function.
A simple and straight forward example. Do you have any more complex queries using these methods?
Let’s hear from you. I try to answer any questions immediately…..
LJH
Hi,
Is it possible to use the same functionality to ADD ‘carriage reutrns’ to the data?
Thanks.
Hi Max,
Yes, it is fairly simple to add carriage returns to the data. For example:
Select ‘Title:’ + CHAR(13) + CHAR(10) + ‘This is my text’
Result:
Title
This is my text
Note though, that the carriage returns won’t be ‘visible’ in Query analyser result sets, but will be visible once rendered on a report/other web page.
LH