contains/patindex - 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

General SQL Server Forums

New to SQL Server Programming

contains/patindex

Author

Topic

ConradK
Posting Yak Master

140 Posts

Posted - 2009-12-07 : 12:50:46

my code

SELECT

case when patindex('%aim%', Products.ProductCode) > 0 then Products.ProductCode end AS "productcode"

FROM Products

ORDER BY products.productcode

works great, but gives me a bunch of blanks where noncompliant(don't contain 'aim') listings would go. How would I say

case when patindex('%aim%', Products.ProductCode) > 0 then Products.ProductCode else ~$DON"T EVEN BOTHER LISTING$~ end AS "productcode"

?????????????

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-07 : 13:10:50

[code]SELECT
Products.ProductCode AS "productcode"
FROM Products
WHERE patindex('%aim%', Products.ProductCode) > 0
ORDER BY products.productcode[/code]

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-12-07 : 13:13:50

Conrad,

You are almost there pal. Just add the where clause where clause - patindex('%aim%', Products.ProductCode) > 0

regards,
Anil

ConradK
Posting Yak Master

140 Posts

Posted - 2009-12-07 : 13:14:17

genius. i totally get where statements now!

I was playing with it in the dark waiting for a response.

THANKS!!!!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-07 : 13:16:08

Np..ur welcome...did you try out the update yet?

ConradK
Posting Yak Master

140 Posts

Posted - 2009-12-07 : 14:21:44

my code

Select

Products.ProductCode AS "productcode"
, options.OptionCatID as "variation theme"

FROM products, options LEFT JOIN Options_ApplyTo ON Options.ID = Options_ApplyTo.OptionID

where patindex(products.productcode, Options_ApplyTo.ProductCode) > 0

ORDER BY products.productcode

it creates multiples of the same entry... why?

ConradK
Posting Yak Master

140 Posts

Posted - 2009-12-07 : 14:32:25

quote:
Originally posted by vijayisonly

Np..ur welcome...did you try out the update yet?

i did not. I am takling many problems at once, and have been working on accessing multiple tables with one query.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-08 : 01:36:17

or

SELECT
Products.ProductCode AS "productcode"
FROM Products
WHERE Products.ProductCode like '%aim%'
ORDER BY products.productcode

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources