How to get the position of the first occurrence of a digit in a postgres select

Question

I have a field that has the SDK name and version and it is not standardized so that I can easily extract just the name and the version. These are contrived values but represent the possible values I am working with:

• JavaScript/2.3.4
• JavaScript/4.3.1
• Android4.6.5
• Android3.2.1
• Swift4.5.3
• Swift/3.1.1.5

As you can see, the use of "/" is not consistent and I need a way to parse the alpha from the numeric consistently so that I end up with two columns like this:

JavaScript     2.3.4
JavaScript     4.3.1
Android        4.6.5
Android        3.2.1
Swift          4.5.3
Swift          3.1.1.5

I have searched for different ways to do this but nothing I have found seems to do what I required.

Ultimately, I need to put this into a Postgres SELECT statement like this:

EDIT

I think the DISTINCT keyword is unneeded and confusing. It was originally part of a COUNT/GROUP BY query, but for simplicity sake, I just want to list all rows with the three columns: sdk, sdk_name and sdk_version separately. From there I will use the best parsing formula from the answers to do as I require.

SELECT sdk, [parse sdk name formula] as "sdk_name", [parse sdk version formula] as "sdk_version"

Furthermore, I don't have a fixed list of SDKs to provide in the query so I am not sure the with/as/values strategy works for me but something I was not aware of and looks useful. I suppose the with/values can just be another SELECT query, though.

Answer 1

Use the regex function substring():

with my_data(sdk) as (
values
    ('JavaScript/2.3.4'),
    ('JavaScript/4.3.1'),
    ('Android4.6.5'),
    ('Android3.2.1'),
    ('Swift4.5.3'),
    ('Swift/3.1.1.5')
)

select 
    substring(sdk from '[^\d/]*') as sdk_name, 
    substring(sdk from '\d.*') as sdk_version
from my_data 

  sdk_name  | sdk_version 
------------+-------------
 JavaScript | 2.3.4
 JavaScript | 4.3.1
 Android    | 4.6.5
 Android    | 3.2.1
 Swift      | 4.5.3
 Swift      | 3.1.1.5
(6 rows)

---

Update.

You can place your select query in the with part (instead of values):

with my_data(sdk) as (
    <select sdk from ...>
)
select 
    substring(sdk from '[^\d/]*') as sdk_name, 
    substring(sdk from '\d.*') as sdk_version
from my_data 

or in the from clause:

select 
    substring(sdk from '[^\d/]*') as sdk_name, 
    substring(sdk from '\d.*') as sdk_version
from (
    <select sdk from ...>
) my_data