SQLTeam.com | Weblogs | Forums

Return String between to known charater


#1

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


#2

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
FROM (
    VALUES('100_390467_481203'),('abc_def'),('abc')
) AS table_name(string)
CROSS APPLY (
    SELECT CHARINDEX('_', string) AS string_first_underscore
) AS assign_alias_names