Inserting comma into a string after particulat len

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

General SQL Server Forums

New to SQL Server Programming

Inserting comma into a string after particulat len

Author

Topic

aoriju
Posting Yak Master

156 Posts

Posted - 2008-10-30 : 04:50:09

I have String.
I want the following ..Example Look like this

Declare @str VARCHAR(1000)
SET @str = 100000000

I want like that -After 3 digits put a comma

@str = 100,000,000

Thanks & Regards
RIju A.O

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 04:57:16

this is a formatting issue. try to do this at your front end application.

aoriju
Posting Yak Master

156 Posts

Posted - 2008-10-30 : 05:21:01

Sorry
I am lokking for sql solution or functions

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-30 : 05:37:45

Visakh16's advice is good. Don't mess with the data returned at the database level unless there is a good reason.

What's your reason?

-------------
Charlie

aoriju
Posting Yak Master

156 Posts

Posted - 2008-10-30 : 05:45:21

We are all Handling in Store Procedures.I f i get a solution i can implement in corresponding store procedures

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 05:48:59

you may use formatting functions available at your front end to achieve this.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-30 : 05:50:49

Ok -- you are moving the problem one step along but still: the data must go somewhere right? You are pulling this information out to something? Some program or front end report?

It won't matter if you do it in a stored procedure or not. The point is that the database isn't a good match for what you want to do. It will be slower and generate more potential problems than just outputting the correct data-type (MONEY or DECIMAL(x,y) in this case?) to whatever front end you have. And then formatting it there.

How does the information from your stored procedure reach however is supposed to get it? Do you go through a website application? some sort of reporting services?

-------------
Charlie

aoriju
Posting Yak Master

156 Posts

Posted - 2008-10-30 : 05:51:14

Sorry Visakh16

I am looking not a frondend solution.This comes in a big procedure.Then i need solution on Backend basic

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-30 : 05:51:59

quote:
Originally posted by visakh16

you may use formatting functions available at your front end to achieve this.

Hi Visakh16 -- looks like we are suggesting the same things again. I'll but out. good luck!

-------------
Charlie

aoriju
Posting Yak Master

156 Posts

Posted - 2008-10-30 : 06:07:47

Sorry Again and Again.....

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-10-30 : 06:19:05

hi Aoriju,

here is a customed made function I used for formatting money to 2 decimal place, commas after 3 digits with $.

CREATE FUNCTION Format_Number (@N decimal(18,2))
RETURNS VarChar(1000)

AS

BEGIN

Declare @NRnd Decimal(18,2)
Declare @Dollar VarChar(30)
Declare @Dollar2 VarChar(30)
Declare @L int
Declare @A int
Declare @B int
Declare @C int
Declare @Cents varchar(20)
Declare @NC varchar(30)

Set @NC = Cast(@N as Nvarchar(30))

Set @NRnd = Round(@N, 0, 1)
Set @Dollar2 = ''
Set @Dollar = Cast(@NRnd as varChar(30))
Set @Dollar = Substring(@Dollar,1, Len(@Dollar) - 3)

Set @C = PATINDEX('%.%',@NC)
Set @Cents = Substring(@NC, @C, 3)
Set @L = Len(@Dollar)
Set @A = @L/3

Set @B = 3
While @A >= 0
Begin
Set @Dollar2 = Substring(@Dollar,@L - @B + 1,3) + ',' + @Dollar2
Set @B = @B + 3
Set @A = @A - 1
End
If Left(@Dollar2,1) = ','
Set @Dollar2 = Substring(@Dollar2, 2, Len(@Dollar2))

Return '$' + Substring(@Dollar2,1, Len(@Dollar2)-1) + @Cents
END

To run it do the following :

Declare @str VARCHAR(1000)
SET @str = 100000000

//you need to convert it to decimal first as my input is a decimal
Declare @str2 int
Select @str2 = convert(decimal(18,2), @str)

//run the function
Select dbo.Format_Number(@str2)

Now if you want to get rid of the .00 and $

edit the last line of code
from
Return '$' + Substring(@Dollar2,1, Len(@Dollar2)-1) + @Cents
To
Return Substring(@Dollar2,1, Len(@Dollar2)-1)

This is what I get when I do the following:
$ 100,000,000.00

and
after edit
100,000,000

this is a big pain in the ass, and you be better off formatting in the front end like what Charlie and Visakh16 suggested... I learn this the hard way. ;) but if you are doing reports on MSSQL directly, this function can be useful

check out my blog at http://www.aquariumlore.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources