CODEWITHSUNDEEP
sqlsql-server
Simhadri
Simhadri

Reputation: 931

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

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

Upvotes: 0

Views: 4551

Answers (2)

Tao
Tao

Reputation: 14006

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

Upvotes: 1

Li0liQ
Li0liQ

Reputation: 11264

I guess, the following could help:

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

Upvotes: 4

Related Questions