String Between Two delimiters ('_')

Question

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.

It is ideal to work with string split function with variable delimiter as `_` http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/19/how-to-split-a-string-by-delimited-char-in-sql-server/ — Ven, Nov 01 '18 at 12:58
Possible duplicate of [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) — Chris Klepeis, Nov 01 '18 at 13:13
Are you splitting by underscore or by dash? You have both "CHARINDEX('-',Field)+1" and "CHARINDEX('_',Field..." in your query — Sorix, Nov 01 '18 at 13:15
@Ven that is the worst possible way to split a string. It is horribly inefficient because loops will make that thing crawl with even half way long strings. [Here](http://www.sqlservercentral.com/articles/Tally+Table/72993/) is a much better option. And if you don't like that one plenty other choices can be found [here](http://sqlperformance.com/2012/07/t-sql-queries/split-strings) — Sean Lange, Nov 01 '18 at 13:19
Take a peek at https://stackoverflow.com/questions/43789578/split-one-column-value-into-multiple-column-values/43789876#43789876 — John Cappelletti, Nov 01 '18 at 13:21

score 2 · Answer 1 · answered Nov 01 '18 at 13:14

2

If it's always three parts you can use a trick with replace and parsename:

SELECT PARSENAME(val, 3) As col1,
       PARSENAME(val, 2) As col2,
       PARSENAME(val, 1) As col3
FROM Table
CROSS APPLY
(
    SELECT REPLACE(Col, '_', '.') As val
) x

answered Nov 01 '18 at 13:14

Zohar Peled

79,642
10
69
121

score 0 · Answer 2 · answered Nov 01 '18 at 13:28

I've used REVERSE before to do something similar with the path and file names.There are some examples Here too.

DECLARE @full VARCHAR(MAX)
SET @full = 'c:\windows\system\sub-folder\somefile_file-stuff.qqq'
SELECT SUBSTRING(@full, 1, LEN(@full)-(CHARINDEX('\',REVERSE(@full))-1)) AS Path

score 0 · Answer 3 · answered Nov 01 '18 at 13:40

Try this, I tend to use variables to get the positions and simply the process

declare @field varchar(300) =  'character1_character2_character3'
declare @char1Pos int =  CHARINDEX('_',@field,0)

--select @char1Pos
declare @char2Pos int = (CHARINDEX('_',@field)+@char1Pos + 1) -1
--select @char2Pos

select  (SUBSTRING(@field,0,@char1Pos)) AS POS1,
SUBSTRING(@field, -- field
          @char1Pos+ 1,--starting position for POS2
          (@char2Pos -1) - @char1Pos) --ENDING POSITION FOR POS2
          AS POS2,
     substring(@field,-- field
                ((@char2Pos +1) ),--starting position for POS3
                len(@field) - ((@char2Pos -1) - @char1Pos))--ENDING POSITION FOR POS2
                 as POS3

Sorix · Accepted Answer · 2018-11-01T18:02:51.773

0

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

edited Nov 01 '18 at 18:02

answered Nov 01 '18 at 13:52

Sorix

850
5
18

Close. Though it creates the same problem I was encountering. When the POS1 has only 3, it displays only 4 from POS2. So on and so forth, it's always plus 1 from POS1, even though the column (middle and end) are variable in length, using the '_' to separate them. – Gary A MacDonald Nov 01 '18 at 15:45
I have updated it and I think this one really does what you want it to do. That being said, it is an ugly solution ;) – Sorix Nov 01 '18 at 18:03
That was it. During the "break", I did manage to find a solution for the left and right values here is the final code I have; SELECT (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 , RIGHT([field],CHARINDEX('_',REVERSE([field]))-1) AS POS3 – Gary A MacDonald Nov 01 '18 at 18:28

score 0 · Answer 5 · answered Nov 01 '18 at 18:34

Final working code;

SELECT (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
, RIGHT([field],CHARINDEX('_',REVERSE([field]))-1) AS POS3
FROM TableName

Thanks all, especially Sorix.

String Between Two delimiters ('_')

5 Answers5