How to extract numbers from a string SQL

Question

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

Which dbms are you using? (Many products have their own, non-ANSI, string functions.) — jarlh
– jarlh, Commented Aug 23, 2021 at 7:16
Im using microsoft sql studio I think its mssql but im not sure — 0brine
– 0brine, Commented Aug 23, 2021 at 7:19

Tim Biegeleisen · Accepted Answer · 2021-08-23 07:25:03Z

3

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
)

answered Aug 23, 2021 at 7:25

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GrahamH · Accepted Answer · 2021-08-23 09:25:30Z

0

if using sql2017 or later... maybe?

CREATE TABLE #yourtable(
   reference VARCHAR(50) NOT NULL PRIMARY KEY
);
INSERT INTO #yourtable(reference) VALUES ('CM|319|14163|||109|405000');
INSERT INTO #yourtable(reference) VALUES ('CMff123|14163|||109AA|4ZXC05000');


SELECT reference,
       TRIM(REPLACE(TRANSLATE(reference,'abcdefghijklmnopqrstuvwxyz',REPLICATE('|',26)),'|',' ')) AS extracted
FROM #yourtable

answered Aug 23, 2021 at 9:25

GrahamH

3602 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to extract numbers from a string SQL

2 Answers 2

Demo

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related