July 16, 2019 at 6:07 am
I want to separate money values in my tables with (/) character
Also separate decimal by (") Character.
Example:
2/498/489"25
help me please
July 16, 2019 at 8:52 am
You would be much better off storing your values in the DB with the correct data types. Then you can format the values any way you want in the presentation layer.
July 16, 2019 at 8:58 am
I want to view only in Select Statement
July 16, 2019 at 9:13 am
Cross post from Stack Overflow (generally it's not well received by communities to cross post, as information ends up being incomplete on the different sites), but I'll reiterate my comment:
That is something for your presentation layer, not SQL Server.
If you let us know the programming language you're using, we can try to help you get the format you're after in the application, but the data type in SQL Server should stay as a decimal.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 16, 2019 at 7:09 pm
No. SQL is based on a tiered architecture, like all modern programming. We do not format data for display in the database tier. Essentially you're trying to write some weird version of COBOL using SQL. Please read any book on RDBMS, and use normalized data.
Please post DDL and follow ANSI/ISO standards when asking for help.
July 17, 2019 at 12:32 pm
As already stated this is normally done in presentation layer.
However if you wish to do it in T-SQL and the datatype is MONEY you can do it this way
REPLACE(REPLACE(CONVERT(varchar(20),[value],1),',','/'),'.','"');
If the datatype is not money, then to use the above, you will have to convert it to MONEY datatype first but watch out for possible rounding problems.
Far away is close at hand in the images of elsewhere.
Anon.
July 17, 2019 at 1:23 pm
No. SQL is based on a tiered architecture, like all modern programming. We do not format data for display in the database tier. Essentially you're trying to write some weird version of COBOL using SQL. Please read any book on RDBMS, and use normalized data.
Ah, be careful now, Joe. If the requirement is to create an export file (for example), there's no better place to do it than at the source, especially when it's such a trivial exercise. It not only cuts down on the load for the RDBMS but it also cuts down on some totally unnecessary network traffic.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2019 at 1:27 pm
I want to separate money values in my tables with (/) character Also separate decimal by (") Character. Example: 2/498/489"25 help me please
I'll make you a deal... tell me what the end game is for this request and I'll show you how to pull it off.
The "End Game" of "because that's what they want" isn't what I'm looking for. Are you creating a special file for someone or trying to satisfy some crazy interface requirement for a GUI that should be shot out of a cannon into a stone wall or what?
There are two reasons for me asking this...
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2019 at 1:34 pm
mohammad362 wrote:I want to separate money values in my tables with (/) character Also separate decimal by (") Character. Example: 2/498/489"25 help me please
I'll make you a deal... tell me what the end game is for this request and I'll show you how to pull it off. The "End Game" of "because that's what they want" isn't what I'm looking for. Are you creating a special file for someone or trying to satisfy some crazy interface requirement for a GUI that should be shot out of a cannon into a stone wall or what? There are two reasons for me asking this...
- It's an unusual request and I'm insanely curious.
- Knowing what the ultimate target for the data is may determine the best method for doing this.
According to their Stack Overflow Question, it's "educational" Jeff. :/
This question is for education purpose. i dont use application. only for learning SQL Server. Thanks
Personally, I'm of the opinion the only thing you can learn from asking to do this in SQL is to learn that you don't do it in SQL.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 17, 2019 at 3:16 pm
Thanks, Thom.
On the subject of whether or not to do this in SQL Server, please see my reply to Joe.
On the subject of what the poster is going to use it for, thanks for the feedback. This question would actually make a pretty good interview question provided that the interviewee had access to BOL (specifically for the CONVERT function). I'm wondering if the "educational" claim is actually someone desperate to answer an interview question. 😀
I took a look at the SO link you provided. The accepted answer uses the god-forsaken FORMAT function. I may have to provide them with a demonstration as to why you shouldn't ever use it in SQL Server.
In the meantime, here's how to do it with performance in mind on SQL Server. Do be advised that this code isn't proper for demonstrating performance. It's only proper for demonstrating functionality.
--===== Create some test data.
-- This is NOT a part of the solution.
SELECT TOP (1000)
SomeMoneyValue = CONVERT(MONEY,CHECKSUM(NEWID())/100.0)
INTO #TestTable
FROM sys.all_columns ac1
;
--===== Display the original value and the formatted value
SELECT SomeMoneyValue
,FormattedMoneValue = REPLACE(REPLACE(CONVERT(VARCHAR(30),SomeMoneyValue,1),',','/'),'.','"')
FROM #TestTable
;
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2019 at 3:31 pm
Ah, apologies. I'd camped out too long on this post and didn't refresh before answering and so missed the fact that Mr. Burrows already posted the correct and for SQL Server. It's especially correct because he avoided the use of FORMAT as I did.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2019 at 3:39 pm
Ah, apologies. I'd camped out too long on this post and didn't refresh before answering and so missed the fact that Mr. Burrows already posted the correct and for SQL Server. It's especially correct because he avoided the use of FORMAT as I did.
🙂
Far away is close at hand in the images of elsewhere.
Anon.
July 17, 2019 at 4:03 pm
If the poster is using the old Sybase MONEY data types, be sure to tell him to look up the rounding errors in multiplication and division that make this illegal for financial uses.
Please post DDL and follow ANSI/ISO standards when asking for help.
July 17, 2019 at 4:27 pm
If the poster is using the old Sybase MONEY data types, be sure to tell him to look up the rounding errors in multiplication and division that make this illegal for financial uses.
Excellent point. I'll also be sure to tell him of the rounding errors that occur due to an automatic rescaling of NUMERIC/DECIMAL data type when the precision tries to exceed 38 as well as the fallacy of doing multiplication and division on any currency that has a scale limited to just 2 or even 4 or 6 or ...
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2019 at 3:13 am
I hate posting on SO because people can edit your stuff and the moderators can be idiots but I thought I'd take a chance and post a performance test along with the REPLACE/CONVERT solution pitted against the FORMAT solution. Here's the code. This is why smart people, like Mr. Burrows, don't use the FORMAT function in SQL Server. Judging by it's performance and the fact that it's actually a part of the .net substrate, I wouldn't use it in .net, either.
--===== Create some test data. =============================================================
-- This is NOT a part of the solution. We're just building test data here.
SELECT TOP (1000000)
SomeMoneyValue = CONVERT(MONEY,CHECKSUM(NEWID())/100.0)
INTO #TestTable
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO
/*******************************************************************************************
In the following code, the output is dumped to a variable to take disk and display times
out of the picture.
*******************************************************************************************/
GO
PRINT'
--===== Run the BASELINE code with no formatting ==========================================';
DECLARE @BitBucket MONEY
;
SET STATISTICS TIME,IO ON;
SELECT @BitBucket =SomeMoneyValue
FROM #TestTable;
SET STATISTICS TIME,IO OFF;
GO
PRINT'
--===== Run the CONVERT code ===============================================================';
DECLARE @BitBucket VARCHAR(30)
;
SET STATISTICS TIME,IO ON;
SELECT @BitBucket = REPLACE(REPLACE(CONVERT(VARCHAR(30),SomeMoneyValue,1),',','"'),'.','/')
FROM #TestTable;
SET STATISTICS TIME,IO OFF;
GO
PRINT'
--===== Run the FORMAT code, take a nap ====================================================';
DECLARE @BitBucket VARCHAR(30)
;
SET STATISTICS TIME,IO ON;
SELECT @BitBucket = REPLACE(REPLACE(FORMAT(SomeMoneyValue, 'C', 'en-us'),',','"'),'.','/')
FROM #TestTable;
SET STATISTICS TIME,IO OFF;
GO
--===== Housekeeping =======================================================================
DROP TABLE #TestTable
;
GO
Here's the results:
(1000000 rows affected)
--===== Run the BASELINE code with no formatting ==========================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#TestTable______________________________________________________________________________
000000621902'. Scan count 1, logical reads 2101, physical reads 0, read-ahead reads 0, lob logi
SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 144 ms.
--===== Run the CONVERT code ===============================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table '#TestTable______________________________________________________________________________
000000621902'. Scan count 1, logical reads 2101, physical reads 0, read-ahead reads 0, lob logi
SQL Server Execution Times:
CPU time = 1047 ms, elapsed time = 1044 ms.
--===== Run the FORMAT code, take a nap ====================================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table '#TestTable______________________________________________________________________________
000000621902'. Scan count 1, logical reads 2101, physical reads 0, read-ahead reads 0, lob logi
SQL Server Execution Times:
CPU time = 32735 ms, elapsed time = 36570 ms.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply