SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
 Oct  8 2022 05:58:25 
 Copyright (C) 2022 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE mytable ( 
[Building Height] VARCHAR(20) ,
[No Storeys] VARCHAR(20) );

INSERT INTO  mytable  VALUES
('8.1m','3'),
('13m','5'),
('7m','6'),
('9.0m','9');
4 rows affected

SELECT * FROM mytable;


Building Height
No Storeys


8.1m
3

13m
5

7m
6

9.0m
9


SELECT 
    REPLACE([Building Height],'m','') step1,
   CAST(REPLACE([Building Height],'m','') as DECIMAL(8,2)) step2,
   [No Storeys]
FROM mytable;


step1
step2
No Storeys


8.1
8.10
3

13
13.00
5

7
7.00
6

9.0
9.00
9