How to get rid of the trailing space

I am having a hard time trying to figure out how to get rid of the trailing space.  I have a field that has a full address.  I need to take out the City, State and Zip.  

addrLines = '2211 Otter Cir  Fountain Valley, CA 92783'
City = 'Fountain Valley'
State = 'CA'
Zip = '92783'

Here is the line of code I am using:

RTRIM(REPLACE(cast(a.addrlines as varchar(250)), a.CITY + ', ' + a.STATE + ' ' + a.ZIP, ''))

Open in new window


Why am I still getting the trailing space?
huerita37Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jose TorresCertified Database AdministratorCommented:
add a RTRIM to a.CITY, a.STATE and a.ZIP
TONY TAYLORCommented:
I question what the character is on the end... can you run this to see if it is a tab, cr, or lf....

ASCII(RIGHT(RTRIM(REPLACE(cast(a.addrlines as varchar(250)), a.CITY + ', ' + a.STATE + ' ' + a.ZIP, '')), 1))

Open in new window

Jim HornMicrosoft SQL Server Data DudeCommented:
Works on my 2012 box, the below returns '2211 Otter Cir'
Declare @addrLines varchar(100) = '2211 Otter Cir  Fountain Valley, CA 92783'
Declare @City varchar(50) = 'Fountain Valley'
Declare @State varchar(2)  = 'CA'
Declare @Zip varchar(10)  = '92783'

SELECT '''' + RTRIM(REPLACE(cast(@addrlines as varchar(250)), @CITY + ', ' + @STATE + ' ' + @ZIP, '')) + ''''

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jose TorresCertified Database AdministratorCommented:
If the CITY, STATE and ZIP are not varchar then you will need to trim those fields as well
TONY TAYLORCommented:
@JimHorn  try this... it will have a trailing tab which will look like white space.  I am wondering if THAT is the issue.

Declare @addrLines varchar(100) = '2211 Otter Cir' + char(9) + ' Fountain Valley, CA 92783' 
Declare @City varchar(50) = 'Fountain Valley'
Declare @State varchar(2)  = 'CA'
Declare @Zip varchar(10)  = '92783'

SELECT '''' + RTRIM(REPLACE(cast(@addrlines as varchar(250)), @CITY + ', ' + @STATE + ' ' + @ZIP, '')) + ''''

Open in new window

Jim HornMicrosoft SQL Server Data DudeCommented:
^^^ correct, that returns '2211 Otter Cir      ' with what appears to be a space, but what is really the tab character.

So, you can use REPLACE(your_string, char(9), '') to weed them out.

A really good idea here would be to get a super-dooper text editor such as UltraEdit.  UE lets you download a free trial.  Then you can load whatever string, hit Ctrl-H to go into Hex view, and view the ANSI character values of every character in a string.  Invaluable when troubleshooting strings with all sorts of goofy characters.

I'm sure other text editors have this functionality, but this is the only one I've worked with.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
huerita37Author Commented:
Tony,

I ran

ASCII(RIGHT(RTRIM(REPLACE(cast(a.addrlines as varchar(250)), a.CITY + ', ' + a.STATE + ' ' + a.ZIP, '')), 1))

Open in new window


And I see 10 on most of them with a few 51 and 52 and a couple other numbers.
huerita37Author Commented:
Jim,

I tried your suggestion

 REPLACE(your_string, char(9), '') 

Open in new window

and it still has the space.
Jim HornMicrosoft SQL Server Data DudeCommented:
Must be another character, so instead of doing the onesie-twosie route trying to guess what's in there I'd go the text editor route, load the string, and know for sure.

I had this problem once running SSIS packages where the source were text files created by a client's UNIX app, and there were all sorts of goofy-riffic characters we had to make them filter out.

Good luck.
Aneesh RetnakaranDatabase AdministratorCommented:
I assume the field is Varchar and not char()
huerita37Author Commented:
I tried to RTRIM() each field, which didn't work.

I tried to cast( as varchar) and that didn't work either.
Aneesh RetnakaranDatabase AdministratorCommented:
Is that a select statement ? or are you updating the fields ?
If its a SELECT statement and if the RTRIM() is not working, you probably has some other characters other than a space.  you may have to replace it.
If you are updating a field,  then you need to ensure that that field is not a CHAR() field.
huerita37Author Commented:
Aneesh,

I will actually be using this in the WHERE clause.  I am first looking to make sure I am taking out what I need to.
huerita37Author Commented:
OMG!!!!  Here is what I ended up with:

REPLACE(REPLACE(REPLACE(cast(a.addrlines as varchar(250)), a.CITY + ', ' + a.STATE + ' ' + a.ZIP, ''), CHAR(13), ''), CHAR(10), '')

Open in new window

huerita37Author Commented:
Thanks for your help everyone!!!  This is crazy data I am dealing with.
TONY TAYLORCommented:
I approve the solution, but just as a comment, the 13 = CR and the 10 = LF.

It is very common to see CRLF combinations and there is even a vbCrLf constant in VBA.

CR = carriage return
LF = line feed

You MAY want to add in "9" as well that is the tab code.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.