Btukfyl

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?

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)

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

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