SQLTeam.com | Weblogs | Forums

Return String between to known charater


Hi everyone,

thank you for taking the time to help me with my issue, what i need to do is get the string between 2 known characters in this example i need to get 390467, i have a filed in our database and i need to get the string between the two _

Example : 100_390467_481203

can anyone help please


You can use CHARINDEX to find the string:

SELECT string,
    CASE WHEN string_first_underscore = 0 THEN '' ELSE 
        SUBSTRING(string, string_first_underscore + 1, 
            ISNULL(NULLIF(CHARINDEX('_', string, string_first_underscore + 1), 0) - 
           string_first_underscore - 1, 8000)) END AS string_between
) AS table_name(string)
    SELECT CHARINDEX('_', string) AS string_first_underscore
) AS assign_alias_names