Let's say i have a column 'Sqr'
39 м²
48 м²
50 м²
15 м²
38 м²
38 м²
35 м²
And I want to remove square meters so that my column will look like this:
39
48
50
15
38
38
35
I've tried this query, but it didn't work
SELECT REPLACE(' 45 м²', ' м²', '');
How should my sqlite query look like?
Without regex replacement, the best you might be able to do would be a smart replace:
UPDATE yourTable
SET Sqr = TRIM(REPLACE(Sqr, 'м²', ''));
This would remove the meters squared symbol in any context, and it also trims any dangling whitespace which might have been created by the removal.
Here's an example that shows 3 ways :-
DROP TABLE IF EXISTS mytable;
CREATE TABLE IF NOT EXISTS mytable (Sqr TEXT);
/*Option 1 */
INSERT INTO mytable VALUES('39 м²'),('48 м²'),('50 м²'),('15 м²'),('38 м²');
UPDATE mytable SET sqr = CAST(sqr AS INTEGER);
SELECT * FROM mytable;
/* Option 2 */
DELETE FROM mytable;
INSERT INTO mytable VALUES('39 м²'),('48 м²'),('50 м²'),('15 м²'),('38 м²');
UPDATE mytable SET sqr = replace(sqr,' м²','');
SELECT * FROM mytable;
/* Option 3 */
DELETE FROM mytable;
INSERT INTO mytable VALUES('39 м²'),('48 м²'),('50 м²'),('15 м²'),('38 м²');
UPDATE mytable SET sqr = substr(sqr,1,instr(sqr,' м²')-1);
SELECT * FROM mytable;
This query:
SELECT Sqr + 0 AS Sqr
FROM tablename;
returns only the starting numeric part of the column, removing all chars starting from the 1st non numeric char.
See the demo.
If you want to update the table:
UPDATE tablename
SET Sqr = Sqr + 0;
See the demo.
Results:
| Sqr |
| --- |
| 39 |
| 48 |
| 50 |
| 15 |
| 38 |
| 38 |
| 35 |
