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


SELECT 
  m.*,
  CAST(value as DECIMAL(8,3)) as [Height in meters]
FROM mytable m
CROSS APPLY STRING_SPLIT([Building Height],'m')
WHERE value<>'';


Building Height
No Storeys
Height in meters


8.1m
3
8.100

13m
5
13.000

7m
6
7.000

9.0m
9
9.000