I have a table os which contains below data
id name
-- ----
1 windows server 2012 R2
2 windows 2016 SQL
3 Oracle linux 7.5
I need to update os table to something like below
id name
-- ----
1 windows server
2 windows
3 Oracle linux
Basically I need to extract the text from name starting from to before the number.
I am not getting an idea about how to do this. How can I do this in a Postgresql query?
The function substring allows to specify regular expression:
So some solution can looks like:
postgres=# select name, trim(substring(name from '[^\d]+')) from the_table;
+----------------------------+----------------+
| name | btrim |
+----------------------------+----------------+
| windows server 2012 R2 foo | windows server |
| windows 2016 SQL | windows |
| Oracle linux 7.5 | Oracle linux |
+----------------------------+----------------+
(3 rows)
More in documentation
You may try this. It starts from beginning and finds anything that's not a digit before a digit and replaces it with the matched string.
SELECT s,RTRIM(regexp_replace (s, '^([^\d]+)\d(.*)$', '\1')) as m
FROM ( VALUES ('windows server 2012 R2'),
('windows 2016 SQL'),
('Oracle linux 7.5' ) ) AS t(s);
s | m
------------------------+----------------
windows server 2012 R2 | windows server
windows 2016 SQL | windows
Oracle linux 7.5 | Oracle linux
(3 rows)
This is a case where the absence of a function that finds a string based on a regex is a bit cumbersome.
You can use regexp_matches() to find the first pattern of the first number in the string, then combine that with substr() and strpos()` to extract everything before that:
select id, substr(name, 1, strpos(name, (regexp_matches(name, '[0-9.]+'))[1]) - 1)
from the_table;
regexp_matches() returns an array of all matches. So wee need to extract the first first match from that array. That's what (regexp_matches(name, '[0-9.]+'))[1] does.
Online example: https://rextester.com/BIOV86746