Truncate characters in a column

I recieve data where I am comparing zip codes from raw data, to a zip code table. The columns I am matching are in integers.

The issue is that in the raw data, sometimes the zip code is extended to more than the 5 characters in the zip code table so I am getting null value returns

A sample of this is in the raw data is:

Zipcode
54303
54304786
78641

Is there a way I can have it compare only the first 5 digits of the zip codes in the raw data zip code column to the zip code table? Or would I have to convert the data type? Or alter or truncate the characters somehow? If so, please include how to do this. Any help would be greatly appreciated. Thank you!

Have a look at the LEFT function.

e.g. LEft (ZipCode, 5) returns the first 5 characters

Stephen,
Thank you! Can I use that with an UPDATE statement?

like UPDATE LEFT (ZipCode, 5) ?

Yes
Update mytable set ZipCode = Left(LongZipCode, 5)

Or

Update ZipCodeTable
set ZipCode = Left(LongZipCode, 5)
From ZipCodeTable Z
Inner Join ZipCodeRaw R

Oops - accidentally hit enter before I finished typing....

Yes
Update mytable set ZipCode = Left(LongZipCode, 5)

Or

Update ZipCodeTable
set ZipCode = Left(LongZipCode, 5)
From ZipCodeTable Z
Inner Join ZipCodeRaw R
On Z.ZipCode = Left(R.LongZipCode, 5)

Thanks again Stephen!
I will give this a shot in the morning and post how it works out.
I really appreciate your expertise on this.

Kudos to this site and it's readers. I seem to get more responsiveness and quality solutions when I post questions here than from the "developers" in my group at work. I am glad this site exists.

I tried using LEFT, but it seems to want to chop off zeros if there is one leading the zip code.

For example 076312140 was changed to 76312, 034353101
was changed to 34353 and seems to be causing null value returns. Is there any way to prevent this, or something else I can try? Thanks again

Try convert it to char(5).

What would that syntax look like? I am still fairly new to this, so pardon my ignorance

select convert(char(5), '034353101')

Is there a means of specifying a column to convert like that?
I have about 1000 zip codes that are too long. Thanks for your help

Can try:

update table set zipcode = convert(char(5), longzipcode)

or

update table set zipcode = substring(longzipcode, 1, 5)

That did it. Thanks for your help!

Just curious on what data type a zip code should be use in a sql table. It was suggested at work that I use integer, that is what was knocking the leading zero off of the zip code.
Should this be char? Thanks again all

Better to use char(5) if you only need 5 digit zip number.

you could use integer as well. For faster searches integer is better.

create table zip(zipcode int)

insert into zip select 07054
insert into zip select 08620

--search
select * from zip where zipcode =08620
select * from zip where zipcode =07054

For displaying on the reports you could add a leading zero if the length is 4.

Great, thank you rmiao!