SQL Script to findout Numeric Value

Question

I need to filter it out the number from my table. for example,

Id      url 
001     /testing
002     /testing2
003     /24678

From the above table, i need to fetch the numeric value ( 24678 - which is dynamically creating) and should update with some string like "my-testing". how to write SQL script for this?

thanks in advance.

• Liyakath

Answer 1

According to your data this will work for t sql but URL will have more than one occurrences of '/' and also so many other factors which I will try to address later but quickly:

DECLARE @url VARCHAR(100)

SET @url = '/testing'
SELECT isnumeric(SUBSTRING(@URL, PATINDEX ( '/%', @URL) + 1, len(@url)))

SET @url = '/testing2'
SELECT isnumeric(SUBSTRING(@URL, PATINDEX ( '/%', @URL) + 1, len(@url)))


SET @url = '/1234'
SELECT isnumeric(SUBSTRING(@URL, PATINDEX ( '/%', @URL) + 1, len(@url)))