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

SQL Server 2000 Forums

SQL Server Development (2000)

PATINDEX ?

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2003-03-13 : 16:41:37

I have a table called AnaNoDups that has two fields. I need to end up with a list of items where ALL of the dash numbers have a status of either Phaseout or Obsolete. I also want to ignore any items with a '%-000'.

Item status
10180-004 ACTIVE
10180-005 ACTIVE
10180-012 OBSOLETE
10180-013 PRECOST
10181-001 PHASEOUT
10181-002 OBSOLETE
10182-000 OBSOLETE
10182-001 ACTIVE
10183-000 RELEASED
10183-001 PHASEOUT
10183-002 OBSOLETE

I should end up with:
10181
10183

I tried to make this work using PATINDEX, but didn't get it to work.

Thanks in Advance!
Lane

motokevin
Starting Member

36 Posts

Posted - 2003-03-13 : 17:03:42

Select DISTINCT LEFT(Item, CHARINDEX('-' , Item))
From AnaNoDups
Where (Status LIKE 'PHASEOUT' Or Status LIKE 'OBSOLETE')
And NOT Item Like '%-000'

Edited by - motokevin on 03/13/2003 17:04:53

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-13 : 17:08:16

BTW, I think you missed one in your final result set.

Try This

CREATE TABLE Table1 (Item varchar(10), Status varchar(10))
Go
INSERT INTO Table1 (Item, Status) SELECT
'10180-004', 'ACTIVE' UNION ALL SELECT
'10180-005', 'ACTIVE' UNION ALL SELECT
'10180-012', 'OBSOLETE' UNION ALL SELECT
'10180-013', 'PRECOST' UNION ALL SELECT
'10181-001', 'PHASEOUT' UNION ALL SELECT
'10181-002', 'OBSOLETE' UNION ALL SELECT
'10182-000', 'OBSOLETE' UNION ALL SELECT
'10182-001', 'ACTIVE' UNION ALL SELECT
'10183-000', 'RELEASED' UNION ALL SELECT
'10183-001', 'PHASEOUT' UNION ALL SELECT
'10183-002', 'OBSOLETE'
Go

SELECT DISTINCT SUBSTRING(Item,1,CHARINDEX('-',Item,1)-1)
FROM Table1
WHERE Status IN ('PHASEOUT','OBSOLETE')
AND CHARINDEX('-000',Item) = 0
Go

Drop Table Table1
Go

Brett

8-)

lane0618
Posting Yak Master

134 Posts

Posted - 2003-03-13 : 19:08:50

Sorry, maybe I wasn't clear enough, but I do definately want to end up with only:

10181
10183

10180 should be included because it has two items that are "Active". I basically want to group by the left(item,5) and see if any of those results have something other than Phaseout or Obsolete. If they do, they should be filterd out.

Thanks.

xpandre
Posting Yak Master

212 Posts

Posted - 2003-03-14 : 00:31:21

y include 183 ??????
10183-000 RELEASED

xpandre
Posting Yak Master

212 Posts

Posted - 2003-03-14 : 04:18:31

select distinct(substring(Item,1,5)) as ii from table1 where status in ('PHASEOUT','OBSOLETE')
and substring(Item,1,5) not in (
select distinct(substring(Item,1,5)) from table1 where status in ('ACTIVE','PRECOST','RELEASED') )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources