October 19, 2008 at 6:27 am
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?
October 19, 2008 at 6:55 am
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
October 19, 2008 at 7:01 am
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).
October 19, 2008 at 7:12 am
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.
October 19, 2008 at 9:23 am
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
October 19, 2008 at 11:31 am
And you must convert this to a character string. Numbers do not allow multiple periods in them.
October 19, 2008 at 4:55 pm
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 removedTruncated Rounded Entered as:
12345678901.000012345678901000012345678901.000012345678901.0000without decimal point
12345678901.000012345678901000012345678901.000012345678901.0000 With decimal point no fractional part
12345678901.234512345678901234512345678901.000012345678901.0000 with fractional part
12345678901.999912345678901999912345678901.000012345678902.0000 with fractional part greater than .5
46905730295.000046905730295000046905730295.000046905730295.0000obarahmeh shortened value
46905730295.547046905730295547046905730295.000046905730296.0000Obrahmeh 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
October 20, 2008 at 3:09 am
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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 20, 2008 at 4:07 am
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
October 20, 2008 at 6:23 am
I think that the best solution is added by krayknot.
Thanks to all...
October 20, 2008 at 6:33 am
EdVassie (10/20/2008)
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'.
Excellent advice. However if the OP insists, then this could be implemented as a function:
[font="Courier New"]DECLARE @TheNumber NUMERIC (15,4), @TheNumberVarChar VARCHAR (20)
SET @TheNumber = 4690573010
SET @TheNumberVarChar = ''
SELECT @TheNumberVarChar = @TheNumberVarChar +
SUBSTRING(REPLACE(CAST(@TheNumber AS VARCHAR (20)), '.', ''), number, 3) +
CASE WHEN LEN(SUBSTRING(REPLACE(CAST(@TheNumber AS VARCHAR (20)), '.', ''), number, 3)) = 3 THEN '.' ELSE '' END
FROM Numbers
WHERE number <= LEN(REPLACE(CAST(@TheNumber AS VARCHAR (20)), '.', ''))
AND (number + 2) % 3 = 0
SELECT @TheNumberVarChar
[/font]
Cheers
ChrisM
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
October 21, 2008 at 7:55 am
I put two records in table tblTest, column aText:
46905730295547
4690573010
For a set-based solution, assuming the value you want to transform is [aText], you could use something like:
SELECT [aText] AS Original
, CASE WHEN convert(INT,LEN(aText)/3) = 0 THEN aText
WHEN convert(INT,LEN(aText)/3) = 1 THEN STUFF(atext,4,0,'.')
WHEN convert(INT,LEN(aText)/3) = 2 THEN STUFF(STUFF(atext,4,0,'.'),8,0,'.')
WHEN convert(INT,LEN(aText)/3) = 3 THEN STUFF(STUFF(STUFF(atext,4,0,'.'),8,0,'.'),12,0,'.')
WHEN convert(INT,LEN(aText)/3) = 4 THEN STUFF(STUFF(STUFF(STUFF(atext,4,0,'.'),8,0,'.'),12,0,'.'),16,0,'.')
WHEN convert(INT,LEN(aText)/3) = 5 THEN STUFF(STUFF(STUFF(STUFF(STUFF(atext,4,0,'.'),8,0,'.'),12,0,'.'),16,0,'.'),20,0,'.')
ELSE ''
END AS Transformed
FROM tblTest
Which returns:
OriginalTransformed
46905730295547469.057.302.955.47
4690573010469.057.301.0
October 22, 2008 at 12:11 am
It is a very good solution,
Thank you very much R. Brush
;):)
October 22, 2008 at 7:25 am
Depending on how big your numbers would be you could convert to [money] and then to string. For instance:
declare @BigNumber bigint
set @BigNumber = 12304208483483
select replace(replace(convert(Varchar(50), cast(@BigNumber as Money), 1), ',', '.'), '.00', '')
July 13, 2011 at 7:44 pm
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
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply