Thankfully(?), in SQL Server 2012+, you can now use FORMAT() to achieve this:

FORMAT(@s,'#,0.0000')

[Query]:

declare @s decimal(18,10);
set @s = 1234.1234567;
select replace(convert(varchar,cast(floor(@s) as money),1),'.00',
    '.'+right(cast(@s * 10000 +10000.5 as int),4))

In the first part, we use MONEY->VARCHAR to produce the commas, but FLOOR() is used to ensure the decimals go to .00. This is easily identifiable and replaced with the 4 digits after the decimal place using a mixture of shifting (*10000) and CAST as INT (truncation) to derive the digits.

[Results]:

|   COLUMN_0 |
--------------
| 1,234.1235 |

But unless you have to deliver business reports using SQL Server Management Studio or SQLCMD, this is NEVER the correct solution, even if it can be done. Any front-end or reporting environment has proper functions to handle display formatting.

(If you are using SQL Server 2012 or newer, please see @wBob's answer for a cleaner approach. The approach outlined in my answer below is only required if you are using SQL Server 2008 R2 or older.)

You don't need (or want) the thousands' separator when converting to NUMERIC, regardless if it is comma, period, or space, so just get rid of them first. Then convert the comma into a period / decimal and you are done:

SELECT CONVERT(NUMERIC(10, 2), 
               REPLACE(
                       REPLACE('7.000,45', '.', ''),
                       ',', '.'
                      )
              ) AS [Converted];

Returns:

7000.45

For the sake of completeness, I should mention that I also tried:

• SET LANGUAGE Greek;

• Looking at various format styles for CONVERT, but nothing applies here.

• The FORMAT function, but the input type must be a numeric or date/time/datetime value (that and it was introduced in SQL Server 2012, so not applicable to SQL Server 2008 R2 or older).

And nothing else seemed to work. I was hoping to find something more elegant than two REPLACE calls, but so far no such luck.

Also, just to mention, while not a pure T-SQL solution, this can also be accomplished via SQLCLR. And, there is a pre-done function that does this in the SQL# library (that I wrote) named String_TryParseToDecimal. This function is available in the Free version, and works in every version of SQL Server starting with SQL Server 2005:

SELECT SQL#.String_TryParseToDecimal('7.000,45', 'el-GR');

Returns:

7000.45000000000000000000

To provide the appropriate culture info, in SQL 2012 there is the FORMAT() function. Here's an example:

Copydeclare @f float = 123456.789;

select
  [raw]      = str(@f,20,3)
 ,[standard] = cast(format(@f, 'N', 'en-US') as varchar(20))
 ,[German] = cast(format(@f, 'N', 'de-DE') as varchar(20))

returns

Copyraw                  |standard   |German     |
---------------------|-----------|-----------|
          123456.789 |123,456.79 |123.456,79 |

You can also specify in the second parameter a custom format string with the same rules as for .NET.

Docs: https://msdn.microsoft.com/en-US/library/hh213505.aspx

In SQL Server 2012 and higher, this will format a number with commas:

select format([Number], 'N0')

You can also change 0 to the number of decimal places you want.

DECLARE @val varchar(50)

set @val = CONVERT(varchar(50), CAST(1112 AS money), 1)
SELECT  left(@val, len(@val) - 3)

This also works with digits after the decimal point:

DECLARE @val varchar(50)

set @val = CONVERT(varchar(50), CAST(1112.56 AS money), 1)
SELECT  left(@val, len(@val) - 3)

Note: as @Mahmoud Gamal points out, formatting is often more suited to be performed in the front-end.

You need to add an appropriate culture argument to FORMAT(). Many European countries use commas as decimal separators, so something like this works:

select FORMAT(12345.8, '#.00', 'fr-fr')