Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

Format a number (string data type) with comma

Is there a built-in function to format a number with commas? like '12345' to '12,345'.
0
yurrea
Asked:
yurrea
1 Solution
 
Victor SpiridonovCommented:
If you mean built into Transact-SQL, the answer is no. You can try to implement formating by yourself using SUBSTRING,DATALENGTH,etc functions.
For example

CREATE PROCEDURE test1
@num_string varchar(20)
AS
DECLARE @new_string VARCHAR(50)
DECLARE @num_len int
DECLARE @position int

SELECT @num_len=DATALENGTH(@num_string)
SELECT @position=@num_len-3
WHILE @num_len>3
 BEGIN
  SELECT @position=@num_len-3
  SELECT @new_string=','+SUBSTRING(@num_string,@position+1,3)+@new_string
  SELECT @num_len=@num_len-3
 END
 SELECT @new_string=SUBSTRING(@num_string,1,@num_len)+@new_string
 SELECT @new_string
GO

0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now