lakhs separator (1,23,45,67,890)...

Question

lakhs separator (1,23,45,67,890)...

yudy.varma

Right there with Babe

Points: 755
More actions
April 23, 2011 at 5:01 am

#238465

n SSRS 2005 there is option to separate thousands(1,234,567,890).but i need in lakhs separator (1,23,45,67,890)... can anybody please explain it clearly

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Isn't this handled by the GUI/app you are using to display the data? Typically separators are handled by the regional settings in windows for me; all numbers in SQL server are still just numbers;

you could make your own ITVF function that used the STUFF function based on the value of the passed in column; is that what you are after?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 2

here is an example that works to 99999.99; from there it's trivial to expand it to higher numbers:

--1,23,45,67,890

ALTER function lakFormatting(@value money)

returns table

as

return

SELECT

CASE

WHEN @value <= 999.99

THEN CONVERT(varchar(30),@value)

WHEN @value <= 9999.99

THEN STUFF(CONVERT(varchar(30),@value),2,0,',')

WHEN @value <= 99999.99

THEN STUFF(CONVERT(varchar(30),@value),3,0,',')

WHEN @value <= 999999.99

THEN STUFF(STUFF(CONVERT(varchar(30),@value),2,0,','),5,0,',')

WHEN @value <= 9999999.99

THEN STUFF(STUFF(CONVERT(varchar(30),@value),3,0,','),6,0,',')

WHEN @value <= 99999999.99

THEN STUFF(STUFF(STUFF(CONVERT(varchar(30),@value),2,0,','),5,0,','),8,0,',')

WHEN @value <= 999999999.99

THEN STUFF(STUFF(STUFF(CONVERT(varchar(30),@value),3,0,','),6,0,','),9,0,',')

--leave to you to flesh out the higher numbers

END AS Val

select myf.Val,originalValue from

(SELECT 1.44 As originalValue UNION ALL

SELECT 23.44 As originalValue UNION ALL

SELECT 321.44 As originalValue UNION ALL

SELECT 4321.44 As originalValue UNION ALL

SELECT 54321.44 As originalValue UNION ALL

SELECT 654321.44 As originalValue UNION ALL

SELECT 7654321.44 As originalValue UNION ALL

SELECT 87654321.44 As originalValue UNION ALL

SELECT 987654321.44 As originalValue) xx

CROSS APPLY dbo.lakFormatting(originalValue) myf

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!