Carriage return in column values

Hi,
How can I find out where the carriage returns are in a column value in a
table? For example, we have an Address column in a Customer table. This
column value can have a maximum of 255 characters where carriage returns are
allowed. How can I find out where those carriage returns are?
Thank you in advance,
DeeOne way is using CHARINDEX, if you only want to first position:
USE tempdb
CREATE TABLE t(c1 int identity, c2 varchar(2000))
INSERT INTO t VALUES('Hello
there')
INSERT INTO t VALUES('Hi everybody
I''ts Doctor Nick')
SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
> Hi,
> How can I find out where the carriage returns are in a column value in a
> table? For example, we have an Address column in a Customer table. This
> column value can have a maximum of 255 characters where carriage returns are
> allowed. How can I find out where those carriage returns are?
> Thank you in advance,
> Dee|||Thank you, Tibor, for your response. Let's put a different twist to it now.
Let's say
I want to identify customer records where the address has 1 or more lines in
the Address column that are greater than 40 characters. Obviously, the text
before each carriage return is considered as one line in the Address column.
"Tibor Karaszi" wrote:
> One way is using CHARINDEX, if you only want to first position:
> USE tempdb
> CREATE TABLE t(c1 int identity, c2 varchar(2000))
> INSERT INTO t VALUES('Hello
> there')
> INSERT INTO t VALUES('Hi everybody
> I''ts Doctor Nick')
> SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
> news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
> > Hi,
> >
> > How can I find out where the carriage returns are in a column value in a
> > table? For example, we have an Address column in a Customer table. This
> > column value can have a maximum of 255 characters where carriage returns are
> > allowed. How can I find out where those carriage returns are?
> >
> > Thank you in advance,
> > Dee
>|||Dealing with unknowns (like unknown number of address lines), I'd suggest writing a scalar user
defined function for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:1F0AF68D-40DA-4F7A-9C84-C086922573FE@.microsoft.com...
> Thank you, Tibor, for your response. Let's put a different twist to it now.
> Let's say
> I want to identify customer records where the address has 1 or more lines in
> the Address column that are greater than 40 characters. Obviously, the text
> before each carriage return is considered as one line in the Address column.
> "Tibor Karaszi" wrote:
>> One way is using CHARINDEX, if you only want to first position:
>> USE tempdb
>> CREATE TABLE t(c1 int identity, c2 varchar(2000))
>> INSERT INTO t VALUES('Hello
>> there')
>> INSERT INTO t VALUES('Hi everybody
>> I''ts Doctor Nick')
>> SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
>> news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
>> > Hi,
>> >
>> > How can I find out where the carriage returns are in a column value in a
>> > table? For example, we have an Address column in a Customer table. This
>> > column value can have a maximum of 255 characters where carriage returns are
>> > allowed. How can I find out where those carriage returns are?
>> >
>> > Thank you in advance,
>> > Dee
>>