Solved

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

Posted on 2014-07-18
9
425 Views
Last Modified: 2014-07-21
I have a sql variable that holds appended record set values that include char(13) + char(10) .

At the very end of the string (not within the string) i will need to remove the char(13) + char(10) .

For example i could have a string variable with the following:

Address: char(13) + char(10) 689 Will Street char(13) + char(10) Columbus char(13) + char(10) GA

But the actual result i would be looking to be removed is bolded below:
Address: char(13) + char(10) 689 Will Street char(13) + char(10) Columbus char(13) + char(10) GA


Thanks,
0
Comment
Question by:MoreThanDoubled
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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;
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Sorry, i missed that this was posted to the SQL Server topic area
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
Comment Utility
For the mentioned  pattern, compare with:

declare @p varchar(100)
set @p='char(13) + char(10) 689 Will Street char(13) + char(10) Columbus char(13) + char(10) GA';
select left(@p,len(@p)-23)+right(@p,3)

Open in new window

0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
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)

Open in new window

0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Apparently that's not what is really desired, since I posted such code two days before hnasr's code.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Passing value to a stored procedure 8 67
using t-sql EXISTS 8 23
GRANT, REVOKE, DENY 4 16
BULK INSERT most recent CSV 19 17
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now