If you don't mind rounding, you can do what you want in one step:
ALTER TABLE dbo.Stringy
ALTER COLUMN latitude decimal (9, 6)
WITH (ONLINE = ON); -- SQL Server 2016 onward
This can be an online operation in SQL Server 2016 or later.
If you need to truncate (not round) things are more tricky. You would need to perform a calculation like:
SELECT
CONVERT
(
decimal(9,6),
ROUND
(
CONVERT
(
decimal(10, 7),
S.latitude
),
6, -- decimal places
1 -- truncate, don't round
)
)
FROM dbo.Stringy AS S;
If there might be some non-numeric values in there, use TRY_CONVERT instead and accept nulls in the new column, or provide a default value with ISNULL.
If the objective is to save space, you'll need to rebuild the table or run DBCC CLEANTABLE (perhaps in small batches until complete) to achieve that. The dropped variable-length string column will continue to take up space until then.
Converting Long Integer to decimal in SQL Server - Software & Applications - Spiceworks Community
How to convert Expression to decimal in SQL or PL/SQL - Databases & Queries - Spiceworks Community
sql server - Convert string numeric values with comma as decimal separator to NUMERIC(10, 2) - Database Administrators Stack Exchange
How to convert integer to decimal in SQL Server query? - Stack Overflow
Videos
(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
What version of SQL Server are you using? From SQL Server 2012 onwards you can use TRY_PARSE with its USING culture argument. You can also use PARSE, the difference being PARSE will fail if the conversion fails and TRY_PARSE will return a NULL, eg
DECLARE @t TABLE ( x VARCHAR(10) )
INSERT INTO @t
VALUES ( '7.000,45' ), ( 'xxx' )
SELECT x,
TRY_PARSE( x AS NUMERIC(10,2) USING 'El-GR' ) x
FROM @t
HTH
SELECT height/10.0 AS HeightDecimal FROM dbo.whatever;
If you want a specific precision scale, then say so:
SELECT CONVERT(DECIMAL(16,4), height/10.0) AS HeightDecimal
FROM dbo.whatever;
SELECT CAST(height AS DECIMAL(18,0)) / 10
Edit: How this works under the hood?
The result type is the same as the type of both arguments, or, if they are different, it is determined by the data type precedence table. You can therefore cast either argument to something non-integral.
Now DECIMAL(18,0), or you could equivalently write just DECIMAL, is still a kind of integer type, because that default scale of 0 means "no digits to the right of the decimal point". So a cast to it might in different circumstances work well for rounding to integers - the opposite of what we are trying to accomplish.
However, DECIMALs have their own rules for everything. They are generally non-integers, but always exact numerics. The result type of the DECIMAL division that we forced to occur is determined specially to be, in our case, DECIMAL(29,11). The result of the division will therefore be rounded to 11 places which is no concern for division by 10, but the rounding becomes observable when dividing by 3. You can control the amount of rounding by manipulating the scale of the left hand operand. You can also round more, but not less, by placing another ROUND or CAST operation around the whole expression.
Identical mechanics governs the simpler and nicer solution in the accepted answer:
SELECT height / 10.0
In this case, the type of the divisor is DECIMAL(3,1) and the type of the result is DECIMAL(17,6). Try dividing by 3 and observe the difference in rounding.
If you just hate all this talk of precisions and scales, and just want SQL server to perform all calculations in good old double precision floating point arithmetics from some point on, you can force that, too:
SELECT height / CAST(10 AS FLOAT(53))
or equivalently just
SELECT height / CAST (10 AS FLOAT)