Search code examples
sqlsubstringcharindex

SQL - Combining 2 Extracting Functions for 1 field

I have a String containing user profiles where the first part of the data is always the same and the second data is always variable. I need to extract the midle part

Example of String ; 

ABC_01_Operator ,XX=ALPHABET-XYZ
ABC_07_Admin ,XX=ALPHABET-XYZ

Data I want to Extract: 

01_Operator
07_Admin

I have 2 Functions that extract a part but I can't find a way to combine them

1) SUBSTRING (MyField, 5, 12) to start from 01 / 07
2) LEFT(MyField, CHARINDEX(',XX', MyField) - 1)
Solution

  • Put (2) as first parameter of (1) like this (set a reasonably high value for substring length):

    select SUBSTRING(LEFT(@MyField, CHARINDEX(',XX', @MyField) - 1),5, 1000);