Posted by whitej77777 on 06/18/07 13:09

On Jun 15, 5:08 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (whitej77...@gmail.com) writes:
> > On Jun 12, 5:14 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> >> (whitej77...@gmail.com) writes:
> >> SELECT substring(col, 1,
> >> len(str) - CASE WHEN str LIKE '%' + char(13)
> >> THEN 1
> >> ELSE 0
> >> END)
>
> > I did mean that the data type was actually text which is what is
> > probably causing the most problem.
>
> So did my SELECT work for you?
>
> I can spot one change that is needed: use datalength() rather than
> len(), as len() does not work past the 8000-character limit.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Well I modified it slightly to get it to work but thank you for the
help. Here is what I used:

substring(fmstreet, 1, datalength(fmstreet) - CASE WHEN fmstreet LIKE
'%' + char(13) + char(10) THEN 2 ELSE 0 END)

Thanks again.

• Next in forum: Delete an excel worksheet only if it exists, in a dts package
• Prev in forum: mdac 2.8 and sql 2005 cursor performace issue
• Thread view: Re: Remove the last carriage return and line feed from sql text field

[Reply to this message]