Modify int result of count in sql server 2005

I am working on sql server 2005 and I am taking count from a specific table

SELECT count(StudentIdReference) as studentCount FROM StudentTable

Right now this select statement is returning me result like 2 or 78 or 790. But in future it will grow 开发者_高级运维rapidly and on UI I don't have sufficient space to show the digit like 1000000. What I want that after 3 digit, I will get the number like 1K or 1.6K, just as we see on stackoverflow.

This would be simpler to be done in the Presentation Layer of your application.

You coud write a user function and do something like this....

CREATE FUNCTION prettyPrint 
(@number int)
RETURNS varchar(30)
AS
BEGIN
declare @return varchar(30)
set @return = cast(@number as varchar(3))
if @number > 1000
    set @return = ''+ cast((@number/1000) as varchar(3)) + '.' + cast((@number % 1000)/100 as varchar(3)) +'K'

-- here must be more 'exceptions' or change all this about the magic number 1000
return @return
end

select dbo.prettyPrint(1500)

SELECT prettyPrint(count(StudentIdReference)) as studentCount FROM StudentTable

As others have stated you should really be doing this in your Presentation Layer not at the DB, however, this will do it for you:

Declare @StudentCount int,
        @StudentCountFormatted varchar(10)

Select @StudentCount = Count(StudentIdReference) as studentCount FROM StudentTable


If @StudentCount > 999
    Begin

        Select @StudentCountFormatted = Convert(Varchar(10), Convert(numeric(19,1), (@StudentCount/ 1000.00))) + 'K'

    End
Else
    Begin
        Select @StudentCountFormatted = @StudentCount
    End


Select @StudentCountFormatted

You need to write your own logic to show such text. There is no built-in method.

I would return the COUNT as-is from SQL Server and leave the formatting up to the UI. This is because:
1) usually easier/performant to do formatting/string manipulation outside of SQL
2) different places in your code using the same query may want to use the data in different ways (maybe not now, but could do in future) so returning the count as-is gives you that flexibility - i.e. won't need 1 version to return the count as an INT and another to return the same as a formatted VARCHAR

You could do it in SQL, but in general I believe in pushing this in to the UI as it's a display/formatting behaviour.

You can always try something like this

SELECT 
    CASE 
    WHEN len(cast(count(*) as varchar(10)))< 4 then cast(count(*) as varchar(10))
    WHEN len(cast(count(*) as varchar(10)))> 4 and len(cast(count(*)as varchar(10)))< 7 
            THEN cast(cast(count(*) / 1000.0 as decimal(10,1)) as varchar(10)) + 'k' 
    ELSE  cast(cast(count(*) / 1000000.0 as decimal(10,1)) as varchar(10)) + 'm'    
END StudentCount
FROM StudentTable