How to extract number which is of variable length from a string using Substring?

Question

I need to extract number from a string column.The length of number in string value is not fixed but it is ended with a special symbol underscore(_).I have tried with Substring but unsuccessful.Please suggest,below sample data for the column

Example String :

FilePath
1002001_Inv_QCR.tiff
100101_Inv_MAN.Jpg



SELECT SUBSTRING(Filepath,1,6)  from Tblfileinfo

Are there multiple filenames/numbers stored in a single entry? If som that would require some sort of looping, recursion, etc. If it's just the one, then a substring expression works. — Tao
– Tao, Commented May 18, 2011 at 16:31

Li0liQ · Accepted Answer · 2011-05-18 16:10:21Z

4

I guess, the following could help:

SELECT
    SUBSTRING(Filepath, 1, CHARINDEX('_', Filepath) - 1)
FROM
   Tblfileinfo

answered May 18, 2011 at 16:10

Li0liQ

11.3k38 silver badges52 bronze badges

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

2 Comments

Simhadri Over a year ago

Thanks for the prompt response, this over killed my problem.How to extract number if the input is like this Inv_100200_Man.txt.

Tao Over a year ago

oookaaay, soooryyy. It was just funny that I had already answered the followup question that @Simhadri was asking... (even though I had answered it by apparently misreading the original question). I am suitably chastized, and will attempt to hold off angering the gods of SO again. All that said, is the answer bad or am I just being punished for childishness?

Tao · Accepted Answer · 2011-05-18 16:29:16Z

1

DECLARE @Value NVarChar(255)
SET @Value = 'Something_1002001_Inv_QCR.tiff'
SELECT SubString(@Value, PatIndex('%[0-9]%', @Value), CharIndex('_', @Value, PatIndex('%[0-9]%', @Value)) - PatIndex('%[0-9]%', @Value))

(note: this would also handle the case where the number is NOT at the start of the name)

Specific to the provided table:

SELECT SubString(Filepath, PatIndex('%[0-9]%', Filepath), CharIndex('_', Filepath, PatIndex('%[0-9]%', Filepath)) - PatIndex('%[0-9]%', Filepath))
FROM Tblfileinfo

edited May 18, 2011 at 16:29

answered May 18, 2011 at 16:14

Tao

14.1k7 gold badges70 silver badges82 bronze badges

Collectives™ on Stack Overflow

How to extract number which is of variable length from a string using Substring?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related