How to extract the number 5005733989 from the string

Question

How to extract this number 5005733989 from this string "employee-process-5005733989-19874"

is it always the third segment ? – Squirrel Feb 19 '21 at 07:26 — Squirrel, Feb 19 '21 at 07:26

score 0 · Answer 1 · answered Feb 19 '21 at 07:32

0

Not the prettiest solution, but we can do this using the base string functions CHARINDEX and SUBSTRING:

SELECT SUBSTRING(col,
                 CHARINDEX('-', col, CHARINDEX('-', col) + 1) + 1,
                 CHARINDEX('-', col, CHARINDEX('-', CHARINDEX('-', col) + 1)) -
                     CHARINDEX('-', CHARINDEX('-', col) + 1) + 1)
FROM yourTable;

Demo

Data:

WITH yourTable AS (
    SELECT 'employee-process-5005733989-19874' AS col
)

answered Feb 19 '21 at 07:32

Tim Biegeleisen

502,043
27
286
360

You need to learn `CROSS APPLY` – Charlieface Feb 19 '21 at 09:28
1

@Charlieface I could counter your comment by saying that SQL Server needs to learn regex `:-)` – Tim Biegeleisen Feb 19 '21 at 09:29
Difference is: the one is easier and far more likely to happen :-) Come on, just rewrite it, it's not difficult – Charlieface Feb 19 '21 at 09:31

score 0 · Answer 2 · answered Feb 19 '21 at 08:13

0

Using STRING SPLIT also we can achieve the Same.

Use top 1,to find the 1st number Segment alone.

DECLARE @STR VARCHAR(MAX)='employee-process-5005733989-19874'

SELECT TOP 1 * FROM STRING_SPLIT(@STR,'-')
WHERE value like '%[0-9]%'

Expected Result:

5005733989

answered Feb 19 '21 at 08:13

Thiyagu

1,260
1
5
14

1

Note that `STRING_SPLIT` generates an _unordered_ set of tuples. Your use of `TOP 1` without an `ORDER BY` clause is therefore not guaranteed to behave as you expect. – Tim Biegeleisen Feb 19 '21 at 09:31

How to extract the number 5005733989 from the string

2 Answers2

Demo