Get value from string in specific place

Question

I'm using T-SQL language and I'm wondering the best way to get specific information from a string.

Example of string :

Category : azd Nom du fichier : 684157 Type de doc : info Id : 21542

The idea is to get the value "684157", which will be always between "fichier :" and "Type".

I have tried with substring and charindex but I miss something.

Here is my code

select substring(com, charindex('fichier : ', com)+len('fichier : '), charindex('Type', com)-charindex('fichier : ', com) + len('Type')) 
from myTable

Answer 1

There might be a neater way of doing this but here's a method using CHARINDEX and SUBSTRING.

SELECT SUBSTRING(com,  
    CHARINDEX('fichier :', com) + LEN('fichier :'), -- start index
    CHARINDEX('Type :', com) - (CHARINDEX('fichier :', com) + LEN('fichier :'))) -- length
FROM MyTable

The startIndex is the index of fichier : plus the length of fichier :. The end index is the index of Type :. For SUBSTRING we need to use the start index and the length. To calculate the length of the substring we use the index of Type : and subtract what we calculated for startIndex.