CODEWITHSUNDEEP
postgresql
Hemadri Dasari
Hemadri Dasari

Reputation: 34014

Postgresql: Extract text before number starts

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?

Upvotes: 1

Views: 1670

Answers (3)

Pavel Stehule
Pavel Stehule

Reputation: 45950

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

Upvotes: 3

Kaushik Nayak
Kaushik Nayak

Reputation: 31746

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)

Upvotes: 2

user330315
user330315

Reputation:

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

Upvotes: 1

Related Questions