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?
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 TorresSenior SQL Server DBACommented:
add a RTRIM to a.CITY, a.STATE and a.ZIP
0
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

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jose TorresSenior SQL Server DBACommented:
If the CITY, STATE and ZIP are not varchar then you will need to trim those fields as well
0
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

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
^^^ 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.
0

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.
0
huerita37Author Commented:
Jim,

I tried your suggestion

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

Open in new window

and it still has the space.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
Aneesh RetnakaranDatabase AdministratorCommented:
I assume the field is Varchar and not char()
0
huerita37Author Commented:
I tried to RTRIM() each field, which didn't work.

I tried to cast( as varchar) and that didn't work either.
0
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.
0
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.
0
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

0
huerita37Author Commented:
Thanks for your help everyone!!!  This is crazy data I am dealing with.
0
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.
1
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.