How to remove char(13) + char(10) from the END of a string

Didn't we just build this string the other day?

That isn't actually "at the very end of the string" is it, since the state characters follow those values.

You might start by using the InstrRev( ) function to look for the last position where those to characters are found together in the sting.

To check your table, you could try:

SELECT [yourField], Left(yourField, instrrev([yourField], chr$(13) & chr$(10))-1)
FROM yourTable
WHERE InstrRev(yourString, chr$(13) & chr$(10)) <> 0

Do you just want to SELECT the rows or do you want to UPDATE the values?

Here's the SELECT to do that; if you press Ctrl-T before running, you can see the affects of the CR+LF in the output results:

SELECT
    address_string AS address_string,
    STUFF(address_string, LEN(address_string) - CHARINDEX(char(10) + char(13), REVERSE(address_string)), 2, ' ') AS address_string_new

FROM (
    select char(13) + char(10) + '689 Will Street' + char(13) + char(10) + 'Columbus' + char(13) + char(10) + 'GA' as address_string
) AS test_data

with t as
(select reverse(address) as address,charindex(char(10) + char(13), reverse(address) - 1) as pos
 from yourtable)
select reverse(left(address,pos) + ' ' + right(address,len(address) - pos - 2)) as newaddress
from t;

Sorry, i missed that this was posted to the SQL Server topic area

How well does that work if the address line was? -
Address: + char(13) + char(10) +  1 Main St. + char(13) + char(10) +  Ames + char(13) + char(10) +  IA

awking00,

For the mentioned  pattern, compare with:

set @p='+ char(13) + char(10) +  1 Main St. + char(13) + char(10) +  Ames + char(13) + char(10) +  IA '
set @p= rtrim(replace(@p,'  ',' '));
select left(@p,len(@p)-26)+right(@p,3)

Select allOpen in new window

hnasr,
I agree that what you have posted will return what is wanted in these two cases, but I think what is really desired is code that will work for any combination of addresses.

Apparently that's not what is really desired, since I posted such code two days before hnasr's code.