How to split columns containing characters and numbers in SQL Server?

Question

In a table I have a column Building Height which contains the heights of buildings. Annoyingly the values are entered like this e.g. '18m', '8m' or '8.1m'.

Ideally, it would have the 'm' in the column name and numeric values in the rows but that's not the case. I do not have admin rights to the table in SQL Server.

I need to perform a data quality rule where I need to perform greater than or less than operators on the Building Height column.

How do I overcome this obstacle?

Example of the table:

Building Height	No Storeys
8.1m	3
13m	5
7m	6
9.0m	9

Data quality rule to perform:

• If building height is less than 11m, then storeys should be less than 5

I have to find rows that do not fit criteria. Obviously the obstacle is the Building Height column containing m next to the number.

I've looked into substring and string_split, but as I do not have admin rights to alter the table I can't really change much, can only query.

Answer 1

2 steps:

1. Remove the m
2. Cast result to DECIMAL

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

see: DBFIDDLE

Using STRING_SPLIT(), you can do:

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

see: DBFIDDLE

Answer 2

SELECT *
FROM BuildingInfo
WHERE 
    CAST(
        CASE 
            WHEN CHARINDEX('m', BuildingHeight) > 0 
            THEN LEFT(BuildingHeight, CHARINDEX('m', BuildingHeight) - 1) 
            ELSE BuildingHeight
        END 
    AS FLOAT) < 11 
    AND NoStoreys >= 5;

See this db<>fiddle.

Answer 3

SOLUTION:

So used REPLACE () to get rid of 'm' as it is always after the number. Then after the WHERE clause I used TRY_CAST() to convert the string into decimal, otherwise you would get an error in converting string to numeric due to some rows containing decimal values.

Example:

WHERE 
  (([No Storeys] >=5) AND (TRY_CAST((REPLACE([Building Height], 'm', '')) AS DECIMAL (22,8)) <11))

Code above will locate rows where the data is not consistent with the rule