How to add "dot" after every three digits in a number in sql 2005

Question

How to add "dot" after every three digits in a number in sql 2005

Viewing 12 posts - 16 through 26 (of 26 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Tim O'Brien-386385 (7/13/2011)
By far the easiest...convert to money, format, strip decimal point:
Print Reverse(SubString(Reverse(Convert(varchar,Convert(Money,1234567891011),1)),4,99))
Prints 1,234,567,891,011

Neither this solution nor the one above it meet the requirements of the OP, which was for processing to run left to right, not right to left. Reversing a number to utilise the money datatype causes loss of trailing whole zeros...

DROP TABLE #Numbers

CREATE TABLE #Numbers (TheNumber BIGINT)

INSERT INTO #Numbers (TheNumber) VALUES (4690573210)

INSERT INTO #Numbers (TheNumber) VALUES (690573210)

INSERT INTO #Numbers (TheNumber) VALUES (90573210)

INSERT INTO #Numbers (TheNumber) VALUES (0573210)

INSERT INTO #Numbers (TheNumber) VALUES (73210)

INSERT INTO #Numbers (TheNumber) VALUES (3210)

INSERT INTO #Numbers (TheNumber) VALUES (210)

INSERT INTO #Numbers (TheNumber) VALUES (10)

INSERT INTO #Numbers (TheNumber) VALUES (0)

INSERT INTO #Numbers (TheNumber) VALUES (12304208483483)

SELECT TheNumber,

Al = replace(replace(convert(Varchar(50), cast(TheNumber as Money), 1), ',', '.'), '.00', ''),

Tim = Reverse(SubString(Reverse(Convert(varchar,Convert(Money,TheNumber),1)),4,99)),

[Reversed money] = REPLACE(STUFF(REVERSE(CONVERT(VARCHAR(30),CONVERT(MONEY,REVERSE(TheNumber)),1)),1,3,''),',','.'),

Calculator.Solution

FROM #Numbers

CROSS APPLY(SELECT Solution =

REPLACE(STUFF(STUFF(STUFF(STUFF(REPLACE(CAST(TheNumber AS CHAR(30)),'.',''),4,0,'.'),8,0,'.'),12,0,'.'),16,0,'.'),'. ',' ')

) Calculator

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

barsuk SSChampion Points: 12280 More actions · Answer 2

Declare @FileName varchar (100)

Set @FileName ='12304208483483'

SELECT REPLACE(Left(Convert(Varchar,Convert(Money,@FileName),1),Len(Convert(Varchar,Convert(Money,@FileName),1)) - 3),',','.')

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

barsuk (7/15/2011)
Declare @FileName varchar (100)
Set @FileName ='12304208483483'
SELECT REPLACE(Left(Convert(Varchar,Convert(Money,@FileName),1),Len(Convert(Varchar,Convert(Money,@FileName),1)) - 3),',','.')

Try '12304208348300'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

obarahmeh (10/20/2008)
I think that the best solution is added by krayknot.
Thanks to all...

Absolutely not. There's no need for the RBAR of that code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Tim O'Brien-386385 (7/13/2011)
By far the easiest...convert to money, format, strip decimal point:
Print Reverse(SubString(Reverse(Convert(varchar,Convert(Money,1234567891011),1)),4,99))
Prints 1,234,567,891,011

That will also likely be one of the slowest methods. "Reverse" is a very expensive function. The Money conversion also doesn't start at the left like the OP wanted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

barsuk SSChampion Points: 12280 More actions · Answer 6

Declare @FileName varchar (100)

Set @FileName ='12304208348300'

SELECT REPLACE(Left(Convert(Varchar,Convert(Money,@FileName),1),Len(Convert(Varchar,Convert(Money,@FileName),1)) - 3),',','.')

12.304.208.348.300

Not sure what the problem was?

I know that this code has limitations, but for the values specified earlier in the thread it works just fine.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

barsuk (7/18/2011)
Declare @FileName varchar (100)
Set @FileName ='12304208348300'
SELECT REPLACE(Left(Convert(Varchar,Convert(Money,@FileName),1),Len(Convert(Varchar,Convert(Money,@FileName),1)) - 3),',','.')
12.304.208.348.300
Not sure what the problem was?
I know that this code has limitations, but for the values specified earlier in the thread it works just fine.

The character count for the "dots" has to start at the left according to the OP's example. Yours starts at the right.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

~~I'm just double checking here... has anyone come up with a solution that actually works for this?~~

Scratch that. I see that R. Brush has one.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Jeff Moden (7/18/2011)
~~I'm just double checking here... has anyone come up with a solution that actually works for this?~~
Scratch that. I see that R. Brush has one.

+2

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

srikant maurya Hall of Fame Points: 3293 More actions · Answer 10

Try This Sql Statment,

DECLARE @String VARCHAR(1000)

SET @String='4690573010230001982347982354378346';

WITH cte

AS (SELECT Cast(Stuff(@String, 4, 0, '.')AS VARCHAR(1000)) AS nbr,

i=4

UNION ALL

SELECT Cast(Stuff(nbr, 4 + i, 0, '.') AS VARCHAR(1000)),

i=i + 4

FROM

cte

WHERE i <= Power(2, Len(@String) / 3))

SELECT nbr AS string

FROM

(SELECT nbr,

Row_number() OVER(ORDER BY i DESC) row_id

FROM

cte

WHERE nbr IS NOT NULL

) t

WHERE row_id = 1

OPTION (MAXRECURSION 0);

Eric M Russell SSC Guru Points: 125561 More actions · Answer 11

obarahmeh (10/19/2008)
Dear all,
If this number " 4690573010" is the result of a sql statement: what should I do to retrieve this number in the following format:
469.057.301.0
I mean: add a dot after every three digits?

If the length of the number is within a fixed range, like a phone number or IP address, then perhaps the transform could be as simple as this example. Numbers with an invalid length could be returned without the transformation.

declare @num varchar(20); select @num = '4690573010';

select

case when len(@num) between 10 and 12

then stuff(stuff(stuff(@num,4,0,'.'),8,0,'.'),12,0,'.')

else @num end;

-----------------------

469.057.301.0

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Eric M Russell (7/19/2011)
If the length of the number is within a fixed range, like a phone number or IP address, then perhaps the transform could be as simple as this example. Numbers with an invalid length could be returned without the transformation.
...

It is simple and it does work - it's also simple to remove the unwanted trailing characters from strings which are shorter than the maximum position into which a dot is inserted - see above.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden