Trim after first 5 digits

Databases
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?

2

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_left

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.

4

use SUBSTRING

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.

So: 12345-9875
Would be trimmed down to: 12345

Apologies if I wasn’t clear before!

6

you didn’t try the LEFT function yet, as guido suggested?

SELECT LEFT(zipcode,5) ...
7

Thanks, I see how that will return the records. How do I trim them permanently?

8

i would advise against doing that

destroying data accuracy is never a good idea

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.

10

sigh…

UPDATE daTable
   SET zipcode = LEFT(zipcode,5)
11

Thanks for that!