removing appended dash

Question

removing appended dash

cthorn112

SSC Eights!

Points: 958
More actions
August 20, 2008 at 8:18 am

#191336

Greetings,
I am trying to join two tables AgentAddresses and ZipCodes on a field called zipcode in to create another table named Agents however several thousand fields in Agent Addresses have a dash (-) appended to the end. So in AgentAdresses it would be 33322- and in ZipCodes it would be 33322. I was wondering if it were possible to write some sort of statement that would remove the dash if it were there because not all the fields in AgetnAddresses have the dash. I would go through and physically remove it however there are thousands of fields like this with the possibility of more coming in. Thanks ahead for reading and any suggestions.
-Chris

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

ctics112 (8/20/2008)
Greetings,
I am trying to join two tables AgentAddresses and ZipCodes on a field called zipcode in to create another table named Agents however several thousand fields in Agent Addresses have a dash (-) appended to the end. So in AgentAdresses it would be 33322- and in ZipCodes it would be 33322. I was wondering if it were possible to write some sort of statement that would remove the dash if it were there because not all the fields in AgetnAddresses have the dash. I would go through and physically remove it however there are thousands of fields like this with the possibility of more coming in. Thanks ahead for reading and any suggestions.
-Chris

join on a case statement, instead of a straing tbl.zipcode=table.zipcode:

you could use a REPALCE function to yank out the dashes as well, i'm asusming your zipcodes have the preceeding zeros if needed so they are all 5 or more chars in length.

select * from AgentAddresses

inner join ZipCodes on CASE

WHEN RIGHT(AgentAddresses.ZipCode,1) = '-'

THEN LEFT(AgentAddresses.ZipCode,5)

ELSE AgentAddresses.ZipCode END = ZipCodes.ZipCode

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Use LIKE in the join:

[font="Courier New"]

SELECT a.zipcode, z.zipcode

FROM AgentAddresses a

LEFT JOIN ZipCodes z ON a.zipcode LIKE z.zipcode+'%'[/font]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

cthorn112 SSC Eights! Points: 958 More actions · Answer 3

Lowell,

I see how this case statement works however it is more complicated than the LIKE statement below. Being fairly new to SQL, I like to keep my code as simple as possible. Thanks for the advice, I might wind up having to use this down the road for a similar situation.

-Chris

cthorn112 SSC Eights! Points: 958 More actions · Answer 4

Chris M.

This seem to work. I was looking at using RTRIM in an IF statement but that did not get me anywhere. I guessed I looked too deep into this simple problem. Thanks for the advice.

-Chris T.

Lowell SSC Guru Points: 323518 More actions · Answer 5

yeah, looking at it again, i might suggest assuming just 5 characters:

JOIN ON LEFT(tbl1.ZIPCODE,5) = LEFT(tbl2.ZIPCODE,5)

that way you ignore bad data, as well as 9 digit zipcodes.