Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How to add "dot" after every three digits in a number in sql 2005 Expand / Collapse
Author
Message
Posted Sunday, October 19, 2008 6:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 6:01 AM
Points: 41, Visits: 309
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?
Post #588243
Posted Sunday, October 19, 2008 6:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
Assuming that the "number" is actually a CHAR or VARCHAR and NOT numeric use the SUBSTRING function for example:
DECLARE @Unk AS VARCHAR(10)
DECLARE @Ans AS VARCHAR(20)
SET @Unk = '14690573010'
SET @Ans = SUBSTRING(@unk,1,3) + '.' + SUBSTRING(@UNK,4,3) + '.' + SUBSTRING(@Unk,7,3) +'.'
+ SUBSTRING(@Unk,10,1)
SELECT @Ans
Result: 146.905.730.1
If it is actually one of the numeric types use the CAST function to convert to VARCHAR format, and then use SUBSTRING

If you would post your T-SQL someone will be able to help you to place the conversion into your T-SQL statement


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #588244
Posted Sunday, October 19, 2008 7:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 6:01 AM
Points: 41, Visits: 309
bitbucket (10/19/2008)
Assuming that the "number" is actually a CHAR or VARCHAR and NOT numeric use the SUBSTRING function for example:


No it is not a char, it is a numeric(15, 4).

Post #588247
Posted Sunday, October 19, 2008 7:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 6:01 AM
Points: 41, Visits: 309
Also your soultion is not ideal because it needs a substring function for each three digits, so if you want to convert a large number like " 46905730295547" to the required format , you will need to write a a new conversion with 5 sub string functions, and so on.
So, I think we need another solution to get every three digits dotted in a number data.
Post #588250
Posted Sunday, October 19, 2008 9:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:20 AM
Points: 12,927, Visits: 32,332
I've done something very similar with a tally table, where i wasinserting avbCrLf after a certain number of characters; I'm away from my hoe PC that has the code, but you can search fro Tally table and look at some of the examples, where you'd build a string fromt eh components from 1 toTally.N where Tally.N = len of the string

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #588257
Posted Sunday, October 19, 2008 11:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:05 PM
Points: 31,284, Visits: 15,750
And you must convert this to a character string. Numbers do not allow multiple periods in them.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #588277
Posted Sunday, October 19, 2008 4:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
obarahmeh I created a table as:
CREATE TABLE [dbo].[DTable](
[Dvalue] [numeric](15, 4) NULL,
[Comment] [varchar](50) NULL
) ON [PRIMARY]
END

Entered various values and then used the following T-SQL statement to return the values

SELECT Dvalue AS 'Original input',
CASE Dvalue
WHEN 0 THEN 'oops'
ELSE REPLACE(CAST(Dvalue AS VARCHAR(25)),'.','')
END AS 'Decimal point removed',
ROUND(Dvalue,0,1) AS 'Truncated', ROUND(Dvalue,0) AS 'Rounded', comment
FROM DTable

Resulting in:
Original input Decimal point removed Truncated Rounded Entered as:
12345678901.0000 123456789010000 12345678901.0000 12345678901.0000 without decimal point
12345678901.0000 123456789010000 12345678901.0000 12345678901.0000 With decimal point no fractional part
12345678901.2345 123456789012345 12345678901.0000 12345678901.0000 with fractional part
12345678901.9999 123456789019999 12345678901.0000 12345678902.0000 with fractional part greater than .5
46905730295.0000 469057302950000 46905730295.0000 46905730295.0000 obarahmeh shortened value
46905730295.5470 469057302955470 46905730295.0000 46905730296.0000 Obrahmeh modified value

How do you want to handle the decimal point and numeirc values to the right of the decimal pont?

By the way if the column were defined as Numeric(15,0) values entered would be equal to the rounded value displayed above.
Entering your value of
46905730295547
results in the error message
Arithmetic overflow error converting numeric to data type numeric


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #588335
Posted Monday, October 20, 2008 3:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 8:26 AM
Points: 2,894, Visits: 3,278
My advice is to do this type of formatting in your application, not in SQL Server.

Although it can be done in SQL Server, application code does this task far more efficiently. Most application classes that support this formatting also respect the Windows regional and user settings for number formatting, so depending on who sees the number it may be formatted as '123.456.789,01' or '123,456,789.01'.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #588424
Posted Monday, October 20, 2008 4:07 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 7, 2011 1:41 AM
Points: 346, Visits: 534

As per the requirement what i understood, here is the code: you need to manipulate it somewhat as per your convinience:

Declare @count INT
DECLARE @finalOutput VARCHAR(30)
DECLARE @dotCount INT
DECLARE @result VARCHAR(40)

SET @dotCount = 1
SET @finalOutput = ''
SET @result = ''

SET @count = 0
WHILE @count <= LEN('123456789123')/3
BEGIN
IF @count = LEN('123456789123')/3
BEGIN
SET @finalOutput = SUBSTRING('123456789123',@dotCount,3)
END
ELSE
BEGIN
SET @finalOutput = SUBSTRING('123456789123',@dotCount,3) + '.'
END
SET @result = @result + @finalOutput

SET @dotCount = @dotCount + 3
SET @count = @count + 1
END
SET @result = SUBSTRING(@result, 1,LEN(@result) - 1)
PRINT (@result)


kshitij kumar
kshitij@krayknot.com
www.krayknot.com
Post #588443
Posted Monday, October 20, 2008 6:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 6:01 AM
Points: 41, Visits: 309
I think that the best solution is added by krayknot.

Thanks to all...
Post #588493
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse