thousand separator - 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 2008 Forums

Transact-SQL (2008)

thousand separator

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-02-01 : 05:09:29

how can I return thousand separator?
i.e.
select valueField from tblMain
so that I get:

123,654...
Thanks

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-02-01 : 05:20:52

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=151482

-- local settings may produce this for you
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.IndianDecimalFormat
(
@Currency decimal(11,2)
)
RETURNS varchar(15)
AS
BEGIN
DECLARE @tCurr varchar(12)
,@RetVal varchar(16)

SELECT @tCurr = CAST(@Currency AS varchar(12))
,@RetVal = ''

;WITH IndianCurrency (pos, ln, ord)
AS
(
SELECT 1, 2, 5
UNION ALL SELECT 4, 3, 4
UNION ALL SELECT 7, 2, 3
UNION ALL SELECT 9, 2, 2
UNION ALL SELECT 11, 2, 1
), Split (Number, ord)
AS
(
SELECT REVERSE(SUBSTRING(REVERSE(@tCurr), pos, ln))
,ord
FROM IndianCurrency
)
SELECT @RetVal = @RetVal + Number + CASE WHEN ord = 4 THEN '.' ELSE ',' END
FROM Split
WHERE LEN(Number) > 0
ORDER BY ord

RETURN LEFT(@RetVal, LEN(@RetVal) - 1)
END
GO

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-01 : 05:22:42

can you do this in your front end application ? Doing it in SQL Server will means you are returning string back to your application

KH
[spoiler]Time is always against us[/spoiler]

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-02-01 : 06:31:54

Did it in th efrond end.
Thank you all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources