Problem using IN clause

[MikeyJinPhx]

Hi All,

Trying to update certain fields where their zipcode falls within a comma delimited list containing the FIRST THREE characters of the zip. Ex. '852,853,854,855'

I'd assume it's something like this:

UPDATE MyTable
SET
Field1 = 'blah',
Field2 = 'blah'
WHERE Zip in ('852,853,854,855')

My prob seems to be that Zip is a 5 character string and I'm not sure of the best way to take the left 3 characters and use those to compare against the list.

Any thoughts?

Thx,
Mike

 

[JayKusch]

UPDATE MyTable
SET
Field1 = 'blah',
Field2 = 'blah'
WHERE SUBSTRING(Zip,3,3) in ('852','853','854','855')

OR FOR NUMERIC CHECK ...

UPDATE MyTable
SET
Field1 = 'blah',
Field2 = 'blah'
WHERE CONVERT(Int,SUBSTRING(Zip,3,3))
IN (852,853,854,855)


Thanks

J. Kusch

 

[MikeyJinPhx]

Thx for the reply Jay! Your solution works great although in a moment of revelation I stumbled upon an alternative solution as well.

UPDATE MyTable
SET
Field1 = 'blah',
Field2 = 'blah'
WHERE LEFT(Zip,3) in ('852','853','854','855')

Is there any reason that one of these solutions might be more efficient than another?

Thx!

 

[JayKusch]

Nope ... both are string manipulations. There is usually more than one solution when it comes to coding T-SQL.

Thanks

J. Kusch

 

[SQLSister]

If this is something that has to happen often, the only way to make it more efficent is to denormalize by adding a field to store only the first 3 characters of the zip. But I wouldn't do this unless I was having problems with the update taking too long and the update was something I did freqently.

Questions about posting. See faq183-874

 

[ESquared]

MikeyJinPhx,

In your first post you wrote

'852,853,854,855'

but now you are writing

'852','853','854','855'

and I am wondering which one you are truly working with.