Search code examples
sql-serverstringsubstringcharindex

String Between Two delimiters ('_')

I am working on splitting a string into its basic components. I have figured out the first part and is working fine;

SELECT(SUBSTRING(Field,0,CHARINDEX('_',Field,0))) AS POS1

What I am currently having a problem with is the 2nd and 3rd parts. The format of the whole string is; character_character_character (where each of these fields can have a varied amount of characters). 

SUBSTRING(Field, CHARINDEX('-',Field)+1, CHARINDEX('_',Field, CHARINDEX('_',Field)+1 - CHARINDEX('_',Field)-1)) AS POS2

This is working in some instances but truncating in others. I've been staring at this for so long that I am solution blind.

Also, going to tackle the third position.

Any advice would be welcomed as to why this does work the way it should.

Solution

  • This should work:

    SELECT Field, SUBSTRING(Field,0,CHARINDEX('_',Field,0)) AS POS1, 
SUBSTRING(SUBSTRING(Field, CHARINDEX('_',Field)+1, LEN(Field)), 0, CHARINDEX('_',SUBSTRING(Field, CHARINDEX('_',Field)+1, LEN(Field)),0)) AS POS2 ,
SUBSTRING(Field, 3 + LEN(SUBSTRING(Field,0,CHARINDEX('_',Field,0))) + LEN(SUBSTRING(SUBSTRING(Field, CHARINDEX('_',Field)+1, LEN(Field)), 0, CHARINDEX('_',SUBSTRING(Field, CHARINDEX('_',Field)+1, LEN(Field)),0))), LEN(Field))  AS POS3
FROM YOUR_TABLE