Problem with patindex() function

Question

I created following table

create table publisher(
  name varchar(20), 
  city varchar(20)
)

I want to run following requirement:

list all the positions of charcter 'a' from name.

For this, Ii ran following query:

select patindex('%a%', name)
  from publisher;

But, it doesn't show proper output. When i put 'city' column instead of 'name' column, it shows right output. What is the problem?

Also, I want to display the name of publishers whose getting minimum and maximum profit. for this, i ran following query:

select name, max(profit), min(profit) 
  from publisher;

It shows the error like that "'name' is not part of aggregate function". what should i do for retrieving name of publishers who getting minimum and maximum profit.

There's no limit to the number of questions you can ask -- these should be separate questions. — OMG Ponies
– OMG Ponies, Commented Mar 1, 2011 at 17:12
For the first question, what output does it give you? Can you give sample data, expected outcome, and actual outcome? — Alex
– Alex, Commented Mar 1, 2011 at 17:14

Thomas · Accepted Answer · 2011-03-01 17:51:19Z

list all the positions of charcter 'a' from name.

To do that, you need a numbers or Tally table. You can simlulate it with a common-table expression if you are using SQL Server 2005 or later:

With Numbers As
    (
    Select 1 As Value
    Union All
    Select Value + 1
    From Numbers
    Where Value <= 100 --replace with max size of Publisher Name
    )
Select P.Name, N.Value
From Numbers As N
    Cross Join Publishers As P
Where Substring( P.Name, N.Value, 1 ) = 'a'
Option ( Maxrecursion 0);

Also, I want to display the name of publishers whose getting minimum and maximum profit.

With ProfitRanks As
    (
    Select Name
        , Rank() Over ( Order By Profit Asc ) As LowestProfitRnk
        , Rank() Over ( Order By Profit Desc ) As HighestProfitRnk
    From Publisher
    )
Select Name, Profit
From ProfitRanks
Where LowestProfitRnk = 1
    Or HighestProfitRnk = 1

Jerry Ritcey · Accepted Answer · 2011-03-01 17:39:17Z

0

The second question needs a group by:

select name, max(profit), min(profit) from publisher group by name

answered Mar 1, 2011 at 17:39

Jerry Ritcey

3112 silver badges8 bronze badges

1 Comment

Martin Smith Over a year ago

They want "The name of publishers whose (sic) getting minimum and maximum profit" NOT "The minimum and maximum profit for each group of publishers sharing the same name"!

Martin Smith · Accepted Answer · 2011-03-01 17:41:30Z

0

For part 2 of your question assuming an index on profit I'd use.

select name,
       profit
FROM   (SELECT TOP(1) with ties name,
                                profit
        FROM   publisher
        ORDER  BY profit) mx
UNION
select name,
       profit
FROM   (SELECT TOP(1) with ties name,
                                profit
        FROM   publisher
        ORDER  BY profit DESC) mn

This could be satisfied with 2 index seeks one at the start and one at the end of the index.

answered Mar 1, 2011 at 17:41

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

2 Comments

Pooja Over a year ago

It's not working, SQL shows the error like that "check the syntax near '(' ". Please give another solution.

Martin Smith Over a year ago

@Pooja - Are you on SQL Server 2000 or something? If so use SELECT TOP 1 without the brackets. If not you must have messed up copying the example as the syntax is fine.

Alexander · Accepted Answer · 2011-03-01 17:55:47Z

0

RE: The issue with the PATINDEX function is because essentially saying you want to know the position of the first occurance of the letter 'a' in the value. That's how PATINDEX works (see MSDN: PATINDEX (Transact-SQL)).

If you want to get the position for each occurance, then you'll need to recursively search the string. Robyn Page & Phil Factor have some great articles on string manipluation at www.Simple-Talk.com

The MAX and MIN functions are aggregate functions. When using aggregate functions, all columns must either be included in an aggregate or in a GROUP BY clause.

This should give you what you're looking for:

select name, max(profit), min(profit)
  from publisher
 group by name

answered Mar 1, 2011 at 17:55

Alexander

2,3182 gold badges25 silver badges33 bronze badges

Collectives™ on Stack Overflow

Problem with patindex() function

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related