TSQL extract part of string with regex

Question

i would make a script that iterate over the records of a table with a cursor and extract from a column value formatted like that "yyy://xx/bb/147011" only the final number 147011and to put this value in a variable. It's possible to do something like that? Many thanks.

Gordon Linoff · Accepted Answer · 2019-02-13 11:32:05Z

3

You don't need a cursor for this. You can just use a query. The following gets everything after the last /:

select right(str, charindex('/', reverse(str)) - 1 )
from (values ('yyy://xx/bb/147011')) v(str)

It does not specifically check if it is a number, but that can be added as well.

answered Feb 13, 2019 at 11:32

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Comments

Suraj Kumar · Accepted Answer · 2019-02-13 11:42:29Z

1

You can also use the below query.

SELECT RIGHT(RTRIM('yyy://xx/bb/147011'), 
CHARINDEX('/', REVERSE('/' + RTRIM('yyy://xx/bb/147011'))) - 1) AS LastWord

answered Feb 13, 2019 at 11:42

Suraj Kumar

5,6748 gold badges24 silver badges45 bronze badges

2 Comments

Giu Over a year ago

pardon my ignorance, but why it's needed to RTRIM the string?

Suraj Kumar Over a year ago

@Giu It has not any major impact.

Yogesh Sharma · Accepted Answer · 2019-02-13 11:31:31Z

1

If numeric value has exact position defined with sample data, then you can do :

SELECT t.*, SUBSTRING(t.col, PATINDEX('%[0-9]%', t.col), LEN(t.col))
FROM table t;

answered Feb 13, 2019 at 11:31

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

TSQL extract part of string with regex

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related