datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
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