DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to apply nested CONVERTs for complex data conversion?

The following SQL Server T-SQL sample scripts demonstrates the use of nested CONVERTs to remove special characters and convert the price result column into money data type first, then into currency format. The CONVERT function and nested CONVERTs are very fast.

-- T-SQL remove special characters from string including space by nesting converts
DECLARE @text nvarchar(128) = '#124 $99^@'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
       REPLACE(REPLACE(REPLACE(REPLACE(@text,
        '!',''),'@',''),'#',''),'$',''),'%',''),
        '^',''),'&',''),'*',''),' ','')

-- 12499

-- SQL Server nested CONVERTs - SQL format money as currency

-- SQL group by - sum aggregate function

USE Northwind;

 

SELECT   ProductName,

         AveragePrice = '$' + CONVERT(VARCHAR,CONVERT(MONEY,

         AVG(od.UnitPrice * (1.00 - Discount)),  1)),

         UnitsSold = SUM(od.Quantity)

FROM     dbo.[Order Details]  AS od,

         dbo.Products         AS p,

         dbo.Orders           AS o

WHERE    od.ProductID = p.ProductID

         AND o.OrderID = od.OrderID

         AND od.UnitPrice > 8.0

GROUP BY ProductName,

         od.ProductID

ORDER BY ProductName

GO

/* Partial results

 

ProductName AveragePrice      UnitsSold

Alice Mutton      $34.23      978

Aniseed Syrup     $9.89 228

Boston Crab Meat  $16.35      1103

Camembert Pierrot $30.12      1577

Carnarvon Tigers  $54.91      539

Chai  $15.80      828

*/

Related article:

Datetime Conversion

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE