Problem using IN clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem using IN clause

Author

Topic

MikeyJinPhx
Starting Member

2 Posts

Posted - 2004-06-28 : 21:12:08

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

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-28 : 21:26:48

Create a table function to parse the comma delimited list. You can then join to it and update where MyTable.Zip LIKE MyFunction.Zip + '%'.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

MikeyJinPhx
Starting Member

2 Posts

Posted - 2004-06-29 : 04:37:24

Thx for the reply Derrick! 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!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-29 : 07:57:28

Yes. The LEFT(Zip,3) will never use an index. :) The function solution will because the left side of your join is maintained.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources