how to pull part of a field? 1

[tigerjade]

I'm stumped; I'm trying to find a way to pull just the zip code, the last part of the field, from a field that holds an address. I could take the field's value, turn it into an array & pull it that way, but there might be several matches for the same zip & then I run into the problem of having to create dynamic variables to pull the different recordsets.

Is there a way to pull all matches in a db based on just PART of a specific field?

Thanks!

tigerjade

 

[mikeadn]

Is the zip code in the same part of the column for every record (left or right part) or is it mixed thoughout the column?

Mike Cormier
May God bless everything you do...

 

[SQLBill]

Give us an example of your data. Are the ZIP CODES all the same length?

You should be able to use RIGHT to pull the part you want.

This will return the last five characters from myfield.

SELECT RIGHT(myfield, 5)

-SQLBill

 

[tigerjade]

I think you're right, Bill. Here's an example:

1273 Pyefinch Road
Suite 145
Raleigh NC 27606

I can always put some javascript in to force the last five characters to be numeric, and notify the user if they've screwed that up, so regulating the field shouldn't be too difficult.

Thanks, both of you!

Tigerjade

 

[SQLBill]

Are you going to accept ZIPCODE+4 inputs?

-SQLBill

 

[tigerjade]

Not in this case; I'm using the zip to search on (we were supposed to have a prox db delivered but it's not here yet so I'm creating backup scenarios), and it won't make a hill o' beans difference with the +4 for searching, so i'm going to, i guess, UN-require that.

tigerjade

 

[SQLBill]

This code should retrieve the postal code regardless of the amount of numbers used. It will look for the first space from the right and then pull everything to the right of that space.

DECLARE @postcode VARCHAR(21)
SET @postcode = 'Raleigh NC 27606-1234'

SELECT RIGHT(@postcode, (CHARINDEX(' ', REVERSE(@postcode)))-1)

-SQLBill

 

[tigerjade]

Cool, thanks Bill! I'll try it out

tigerjade

 

[SQLBill]

Here's yet another way that I think will be better (I posted this yesterday, but it didn't show up):

DECLARE @postcode VARCHAR(21)
SET @postcode = 'Raleigh NC 27606-1234'

SELECT SUBSTRING(@postcode, 1, (PATINDEX('%[0-9]%', @postcode)-1)) AS [Post Code],
       SUBSTRING(@postcode, (PATINDEX('%[0-9]%', @postcode), LEN(@postcode)) AS [Address]
FROM mytable

-SQLBill

 

[SQLBill]

Oooppps, my last post was actually for another question on another site. That's why it didn't show up here. But if it helps you also, great.

-SQLBill