Re: Single Letter Search

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Steve Kass (skass_at_drew.edu)
Date: 06/24/04 

Date: Thu, 24 Jun 2004 02:02:35 -0400

Khurram,

  Here's a modification of Roji's solution that will count all the
titles starting with numbers as a single group:

--sample data
create table t
(Title varchar(50))

insert into t (title)
select 'Die Hard 2' union all
select 'Die Hard 1' union all
select 'Three Pigs' union all
select 'Microsoft SQL' union all
select '1980' union all
select '2980' union all
select '2984'

--letters table
create table categories (
  i int,
  n varchar(10),
  c varchar(10)
)

insert into categories(i,n,c)
select top 26
  OrderID-10247,
  char(97+OrderID-10248),
  char(97+OrderID-10248)
from Northwind..Orders
order by OrderID
insert into categories(i,n,c) values (0, 'Numbers', '0123456789')

--query
select categories.n, count(t.title) cnt
from categories left outer join t
on categories.c like '%'+substring(t.title,1,1)+'%'
group by categories.n
order by max(categories.i)
go
drop table categories
drop table t

Steve Kass
Drew University

Khurram Chaudhary wrote:

>Hi,
>
>What I'm trying to do is have a stored procedure output single letters and
>numbers based on the results of a table. For example, let's say you have the
>following:
>
>Book Title Intended
>Result
>Die Hard 2 D
>Three Pigs T
>Microsoft SQL M
>1980 1
>
>However, not only do I want to get just the first letters of each Book
>Title, but I would like to have a 0 displayed if there are no instances of
>that letter/number and 1 if there is, shown as follows:
>
>A 0
>B 0
>C 0
>D 1
>and so on ...
>
> Finally, I would like group numbers together into a group called 'Numbers'.
>If there is one title that starts with 1 but the rest of the numbers are 0,
>the result should be that Numbers is 1. It should like something like this:
>
>Numbers 1
>A 0
>B 0
>C 0
>D 1
>and so on ....
>
>Does anyone have any ideas? Thanks in advance.
>
>Khurram
>
>
>
>