ggeiger — 2012-05-07T11:28:55-04:00 — #1
I'm looking for some syntax help to remove characters in a string. I have a field called zipcode, which has some different formats in it. I'd like to remove any characters after the first 5.
for example: 12345-1234
would return: 12345
I've looked at TRIM() and RTRIM(), but I don't see how to do the above when I don't know what the sting contains, other than it's length?
guido2004 — 2012-05-07T11:53:03-04:00 — #2
ggeiger — 2012-05-07T14:07:18-04:00 — #3
Thanks for the link. I see this will return the what I'm looking for.
What I need to do is trim the field, so the left most characters are removed.
r937 — 2012-05-07T14:36:06-04:00 — #4
ggeiger — 2012-05-07T14:59:36-04:00 — #5
This might be easier than I'm making it, and it's not helping that I just confused things by writing the "left most characters". Sorry about that.
I have a field called zipcode. We'd like to trim the data in that field, for every record in the DB, to have no more than 5 characters. Preferably, the first five.
Would be trimmed down to: 12345
Apologies if I wasn't clear before!
r937 — 2012-05-07T15:01:31-04:00 — #6
you didn't try the LEFT function yet, as guido suggested?
SELECT LEFT(zipcode,5) ...
ggeiger — 2012-05-07T15:11:40-04:00 — #7
Thanks, I see how that will return the records. How do I trim them permanently?
r937 — 2012-05-07T15:26:15-04:00 — #8
i would advise against doing that
destroying data accuracy is never a good idea
ggeiger — 2012-05-07T15:33:48-04:00 — #9
I agree that removing good, or accurate data is a bad idea.
In this case, the "zip +4" data is not only bad data, but is causing the client problems with the USPS and needs to be removed from those records that have extra characters after the first 5.
r937 — 2012-05-07T15:59:29-04:00 — #10
SET zipcode = LEFT(zipcode,5)
ggeiger — 2012-05-07T16:16:46-04:00 — #11
mittineague — 2014-09-23T03:51:04-04:00 — #12
This topic is now archived. It is frozen and cannot be changed in any way.