T-SQL Find length of word within a string

Question

With PATINDEX I can find the first occourence of a pattern in a string, say a number - in the string there is several matches to my pattern

My question is how can I find the end position of the first occourence of that pattern in a string?

DECLARE @txt VARCHAR(255)
SET @txt = 'this is a string 30486240 and the string is still going 30485 and this is the end'
PRINT SUBSTRING(@txt,PATINDEX('%[0-9]%',@txt),8)

My problem is, I dont want to put in the 8 in manually, I want to find the length of the first number

Using SQL Server 2012

Are you trying to find something like the LastIndexOf? Something has a bad smell from a sql side when you have to do that sort of thing but you can do it easily enough with REVERSE and PATINDEX. — Sean Lange, Mar 18 '15 at 14:01
Are you just trying to extract the first occurence of a number in the string? — Giorgos Betsos, Mar 18 '15 at 14:09
If it always ends with a space you can use CHARINDEX and a start_location — paparazzo, Mar 18 '15 at 14:10
Yes basicly - for now, but the goal is I want to extract all numbers from the string. — user2199192, Mar 18 '15 at 14:11
possible duplicate of [Extract numbers from a text in SQL Server](http://stackoverflow.com/questions/9629880/extract-numbers-from-a-text-in-sql-server) — Tanner, Mar 18 '15 at 14:25

score 0 · Answer 1 · answered Mar 18 '15 at 15:19

Try this, it should return the first number from your text:

DECLARE @txt VARCHAR(255)
SET @txt = 'this is a string 30486240 and the string is still going 30485 and this is the end'

DECLARE @startIndex INTEGER
SELECT @startIndex = PATINDEX('%[0-9]%',@txt)

DECLARE @remainingString NVARCHAR(MAX)
SELECT @remainingString = substring(@txt, @startIndex, LEN(@txt) - @startIndex)

DECLARE @endingIndex INTEGER
SELECT @endingIndex = PATINDEX('%[a-zA-Z]%', @remainingString) - 1

SELECT RTRIM(SUBSTRING(@txt, @startIndex, @endingIndex))

This query will work as long as you don't have letters "embedded" in your numbers, like 30486a24b0

score 0 · Answer 2 · answered Mar 18 '15 at 15:20

Here is one solution when you don't know the length of the substring:

SELECT Left(
             SubString(@Data, PatIndex('%[0-9.-]%', @Data), 8000), 
             PatIndex('%[^0-9.-]%', SubString(@Data, PatIndex('%[0-9.-]%', @Data), 8000) + 'X')-1)

Source: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server/

I had to run through the exercise multiple times and kept thinking the blog post was wrong, before noticing the caret in the second PATINDEX.

T-SQL Find length of word within a string

2 Answers2