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

Transact-SQL (2000)

count

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2006-05-10 : 05:35:18

Hi,

There is a select query which returns a count as below:

select
@count = count(typecode)
from
tbl_Types
where
typecode = @typecode

depending on the count, this is what needs to be done...
If there is a count of 1 to 26 then I would like to use A-Z and then anything from 26 onwards needs to use 1-infinity

For example, let's say the count returns 4, so now I would like the sql to return D since D is the fourth letter in the alphabet.
Let's say the count returns 8, so now I would like the sql to return H since H is the 8th alphabet.
And if for example the count is something like 27 then the sql should return 1 since a-z takes you to 1-26 and now should use number 1.
And a last example, if the coount is 30, then sql should return the number 4
Hope this is clear.
Thanks

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-10 : 05:46:34

Create a function which somewhat looks like this..


Create Function ReturnChar(@Nos int )
Returns char(1) As 
Begin 
Declare @var Char(1)
if @Nos > 26 
  Select @Nos = @Nos%26
	
Select @var = Case @Nos When 1 Then 'A' 
                 When 2 Then 'B'  
               .................
               .................
               .................
               .................
               .................
               When 26 Then 'Z' End 

return @var	
End

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2006-05-10 : 05:50:55

yes, this is fine but I am trying to avoid having a big case statement. May be there is a function in sql to return the letter for a number?
Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-05-10 : 05:59:08

[code]
select ascii('A'), ascii('A')-64,
ascii('a'), ascii('a')-96
[/code]

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-10 : 06:44:53

How about this ??


Create Function ReturnChar(@Nos int )
Returns char(1) As 
Begin 
Declare @var Char(1)
if @Nos > 26 
  Select @Nos = @Nos%26

Set @Nos = @Nos + 64
	
Select @var = Char(@Nos)

return @var	
End

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-10 : 06:49:42

declare @t table (i int)
insert @t select 4 union select 8 union select 27 union select 30

select i, case when i < 26 then char(i+64) else cast(i - 26 as varchar(10)) end as Code from @t

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources