How to extract numbers from a string SQL

Ts there a way to extract numbers from a string?

In my database there is a column called Reference and it contains multiple numbers

CM|319|14163|||109|405000

Is there a way to get the first number like this?

select CM|%s|... as ParentId from table

So the output should be 319

And maybe even multiple like

select CM|...|%s|... as SiblingId, CM|%s|... as ParentId from table

14163 319

Answer

We might be able to use PATINDEX here along with a substring operation. We can find the position of the first number in the string, and then take a substring until one position past the first occurrence of a number followed by a pipe character.

SELECT SUBSTRING(val,
                 PATINDEX('%[0-9]%', val),
                 PATINDEX('%[0-9]|%', val) - PATINDEX('%[0-9]%', val) + 1)
FROM yourTable;

Demo

Data:

WITH yourTable AS (
    SELECT 'CM|319|14163|||109|405000' AS val
)