CHARINDEX Start Position: Find the Second Delimiter

I use the CHARINDEX start position to find the next occurrence of a fixed delimiter. I check the first match before starting the second search. A zero not-found position otherwise sends that search back to the beginning.

Start after the first delimiter

CHARINDEX positions are one-based. In AA|BB|CC, the first bar is at position three, so the next search starts at four. That search returns six.

The third argument changes where scanning begins; it doesn’t make the returned position relative to that start. The reported six still refers to the original input text. I keep both positions in the output because they explain the extracted field more clearly than the field alone.

WITH Inputs AS (
 SELECT CaseId, InputText
 FROM (VALUES
  (1,CAST(N'AA|BB|CC' AS nvarchar(40))),
  (2,N'AA|BB'), (3,N'AA'), (4,N'|B|'),
  (5,N'A||C'), (6,N''), (7,NULL)
 ) AS v(CaseId,InputText)
), FirstMatch AS (
 SELECT *, CHARINDEX(N'|',InputText) AS FirstDelimiter
 FROM Inputs
), Positions AS (
 SELECT *, CASE WHEN FirstDelimiter>0
   THEN CHARINDEX(N'|',InputText,FirstDelimiter+1)
   ELSE CAST(NULL AS int) END AS SecondDelimiter
 FROM FirstMatch
)
SELECT CaseId, InputText, FirstDelimiter, SecondDelimiter,
 CAST(CASE WHEN SecondDelimiter>FirstDelimiter
  THEN SUBSTRING(InputText,FirstDelimiter+1,
    CASE WHEN SecondDelimiter>FirstDelimiter
      THEN SecondDelimiter-FirstDelimiter-1 ELSE 0 END)
  ELSE NULL END AS nvarchar(40)) AS MiddleField
FROM Positions
ORDER BY CaseId;

Treat not-found and missing input separately

The function returns zero when it cannot find the requested substring, and NULL when a search argument is NULL. In the example, SecondDelimiter is deliberately NULL when there is no first delimiter. When the first exists but the second doesn’t, it is zero.

Those outcomes carry different information. Don’t collapse those cases into an empty string. Missing structure would then look like a valid field without contents.

Extract a field only after validating its bounds

The middle field starts one character after the first bar. Its length is the gap between the bars minus one. Adjacent bars therefore describe an empty field, with a valid zero length.

The example protects the length expression independently so it cannot become negative when the structure is absent. The outer CASE then distinguishes that absent structure from an intentionally empty field. No row is discarded to hide an inconvenient boundary case.

Check the seven supplied cases

The expected first row yields BB, while AA|BB has no second bar and yields NULL. The leading and trailing bars in |B| enclose B.

The adjacent bars in A||C enclose an empty value. AA and the empty input have no first bar, and the final row preserves a missing input. These expectations describe the supplied VALUES rows; they are not a claim that arbitrary imported data has the same structure.

Use a literal delimiter rather than a pattern

CHARINDEX searches for the specified text, not a wildcard expression. A percent sign used as the search string is therefore a literal percent sign.

Comparisons follow the input collation, and supplementary-character collations affect character counting. The bars in this example avoid those language-sensitive details. For a delimiter with several characters, move the next start by the delimiter’s character length rather than automatically adding one.

Keep the format contract narrow

This example recognizes two literal bars. It doesn’t implement quoted CSV, escape sequences or a nested document format.

If a bar can be escaped or embedded inside a quoted field, the application needs a fuller parsing contract. I first decide what a missing separator and an empty field mean, then retain those distinctions in the result. That makes the next validation step a business decision rather than an accidental consequence of string arithmetic.

Check the first match before the second search, and the field comes out clean.

A second search is not a parser by itself, it is a step that needs a first-match guard.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.