sql server programming: Single Letter Search -- sql server programming

Re: Single Letter Search

Steve Kass
6/24/2004 2:02:35 AM

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

[quoted text, click to view]

Re: Single Letter Search

Uri Dimant
6/24/2004 7:51:28 AM

Khurram
If you post DDL+ sample data + expected result so it will be much easily to
solve the problem.

[quoted text, click to view]

Re: Single Letter Search

Aaron [SQL Server MVP]
6/24/2004 8:34:19 AM

Some ideas here...
http://www.aspfaq.com/2352

--
http://www.aspfaq.com/
(Reverse address to reply.)

[quoted text, click to view]

Re: Single Letter Search

Roji. P. Thomas
6/24/2004 10:38:43 AM

Try this.

uSE PUBS
go
--Select * FROM titles
CREATE TABLE #Letters(Letter CHAR(1))
INSERT INTO #Letters VALUES('0')
INSERT INTO #Letters VALUES('1')
INSERT INTO #Letters VALUES('2')
INSERT INTO #Letters VALUES('3')
INSERT INTO #Letters VALUES('4')
INSERT INTO #Letters VALUES('5')
INSERT INTO #Letters VALUES('6')
INSERT INTO #Letters VALUES('7')
INSERT INTO #Letters VALUES('8')
INSERT INTO #Letters VALUES('9')
INSERT INTO #Letters VALUES('A')
INSERT INTO #Letters VALUES('B')
INSERT INTO #Letters VALUES('C')
INSERT INTO #Letters VALUES('D')
INSERT INTO #Letters VALUES('E')
INSERT INTO #Letters VALUES('F')
INSERT INTO #Letters VALUES('G')
INSERT INTO #Letters VALUES('H')
INSERT INTO #Letters VALUES('I')
INSERT INTO #Letters VALUES('J')
INSERT INTO #Letters VALUES('K')
INSERT INTO #Letters VALUES('L')
INSERT INTO #Letters VALUES('M')
INSERT INTO #Letters VALUES('N')
INSERT INTO #Letters VALUES('O')
INSERT INTO #Letters VALUES('P')
INSERT INTO #Letters VALUES('Q')
INSERT INTO #Letters VALUES('R')
INSERT INTO #Letters VALUES('S')
INSERT INTO #Letters VALUES('T')
INSERT INTO #Letters VALUES('U')
INSERT INTO #Letters VALUES('V')
INSERT INTO #Letters VALUES('W')
INSERT INTO #Letters VALUES('X')
INSERT INTO #Letters VALUES('Y')
INSERT INTO #Letters VALUES('Z')

SELECT L.Letter,
Min(CASE WHEN T.Title Is NULL Then 0 Else 1 End) As HasBook
FROM #Letters L
LEFT OUTER JOIN Titles T
ON L.Letter = LEFT(T.Title,1)
GROUP BY L.Letter

drop TABLE #Letters

If Its what you want, you owe me a beer ;)

--
Roji. P. Thomas
SQL Server Programmer
[quoted text, click to view]

Re: Single Letter Search

Khurram Chaudhary
6/24/2004 10:42:36 AM

Hi Guys,

Thanks for all the help. I ended up trying Roji's approach and it works
great. Now, I just need to know how to group all the numbers into a group
called 'Numbers'.

Here's the stuff you'll need. BTW Roji, I owe you a beer ;).

Khurram

uSE PUBS
go
--Select * FROM titles
CREATE TABLE #Letters(Letter CHAR(1))
INSERT INTO #Letters VALUES('0')
INSERT INTO #Letters VALUES('1')
INSERT INTO #Letters VALUES('2')
INSERT INTO #Letters VALUES('3')
INSERT INTO #Letters VALUES('4')
INSERT INTO #Letters VALUES('5')
INSERT INTO #Letters VALUES('6')
INSERT INTO #Letters VALUES('7')
INSERT INTO #Letters VALUES('8')
INSERT INTO #Letters VALUES('9')
INSERT INTO #Letters VALUES('A')
INSERT INTO #Letters VALUES('B')
INSERT INTO #Letters VALUES('C')
INSERT INTO #Letters VALUES('D')
INSERT INTO #Letters VALUES('E')
INSERT INTO #Letters VALUES('F')
INSERT INTO #Letters VALUES('G')
INSERT INTO #Letters VALUES('H')
INSERT INTO #Letters VALUES('I')
INSERT INTO #Letters VALUES('J')
INSERT INTO #Letters VALUES('K')
INSERT INTO #Letters VALUES('L')
INSERT INTO #Letters VALUES('M')
INSERT INTO #Letters VALUES('N')
INSERT INTO #Letters VALUES('O')
INSERT INTO #Letters VALUES('P')
INSERT INTO #Letters VALUES('Q')
INSERT INTO #Letters VALUES('R')
INSERT INTO #Letters VALUES('S')
INSERT INTO #Letters VALUES('T')
INSERT INTO #Letters VALUES('U')
INSERT INTO #Letters VALUES('V')
INSERT INTO #Letters VALUES('W')
INSERT INTO #Letters VALUES('X')
INSERT INTO #Letters VALUES('Y')
INSERT INTO #Letters VALUES('Z')

SELECT L.Letter,
Min(CASE WHEN T.Title Is NULL Then 0 Else 1 End) As HasBook
FROM #Letters L
LEFT OUTER JOIN Titles T
ON L.Letter = LEFT(T.Title,1)
GROUP BY L.Letter

[quoted text, click to view]

Re: Single Letter Search

Vishal Parkar
6/24/2004 11:11:04 AM

hi khurram,

See following example.

--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'

--query
select x.char, isnull(y.cnt,0) cnt
from
(select 'a' 'char' union all
select 'b' 'char' union all
select 'c' 'char' union all
select 'd' 'char' union all
select 'e' 'char' union all
select 'f' 'char' union all
select 'g' 'char' union all
select 'h' 'char' union all
select 'i' 'char' union all
select 'j' 'char' union all
select 'k' 'char' union all
select 'l' 'char' union all
select 'm' 'char' union all
select 'n' 'char' union all
select 'o' 'char' union all
select 'p' 'char' union all
select 'q' 'char' union all
select 'r' 'char' union all
select 's' 'char' union all
select 't' 'char' union all
select 'u' 'char' union all
select 'v' 'char' union all
select 'w' 'char' union all
select 'x' 'char' union all
select 'y' 'char' union all
select 'z' 'char' ) X left outer join (select left(title,1) 'title',
count(*) 'cnt'
from t
group by left(title,1) ) Y on x.char = y.title

--
Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com

Re: Single Letter Search

Aaron [SQL Server MVP]
6/24/2004 11:24:20 AM

Well, you could have a bit column on #Letters that indicates whether it's a
number or not, and then use a CASE to group by whether number = 1 or number
= 0.

Also, why not create a permanent table? Why re-create the exact same #temp
table every time?

--
http://www.aspfaq.com/
(Reverse address to reply.)

[quoted text, click to view]

Re: Single Letter Search

Steve Kass
6/24/2004 3:00:27 PM

Khurram,

Did my suggestion work to take care of the Numbers group?

SK

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.