Create a built in INSTR function in SQL Server to search for nth instance of a character within a string - by douglasholt

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 817191 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 2/11/2014 9:59:03 AM
Access Restriction Public


The syntax for the Oracle/PLSQL INSTR function is:

INSTR( string, substring [, start_position [,  nth_appearance ] ] )

This fourth input parameter allows you to search a string for the nth occurrence of a character.

The T-SQL CHARINDEX function does allow for a third input parameter to specify starting location but this does not allow searching for nth occurrence of a character. 

CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )

This is especially useful when parsing long strings with specific delimiters such as comma, pipe, etc. 
Sign in to post a comment.
Posted by douglasholt on 5/14/2014 at 6:57 AM
Thank you GaryHughes_PWC, but often I will need to find an nth instance (aka occurrence) much higher than the 32 limit here. It is very common for HL7 messages to contain dozens of instances of a single character (normally pipe delimited) that would quickly exceed the 32 iterations of the tinyint variable.

Thank you Jos de Bruijn - Implementing/Copying this function from PL-SQL to T-SQL would seem to be low hanging fruit for MicroSoft and would help improve parity with a very popular RDBMS (cough Oracle).
Posted by Microsoft on 5/9/2014 at 1:19 PM
After carefully evaluating all of the suggestion items in our pipeline, we are closing items that we will not implement in the near future due to current higher priority items. We will re-evaluate the closed suggestions again in the future based on the product roadmap.
Thanks again for providing the product suggestion and continued support for our product.
Jos de Bruijn - SQL Server PM
Posted by GaryHughes_PWC on 2/21/2014 at 1:40 PM
The section that shows:

IF @NTH >32
        SET @NTH = 32 --> This is the maximum amount of recursive iterations.

Should be

IF @NTH >31
        SET @NTH = 31 --> This is the maximum amount of recursive iterations.

This is because it counts the current function as 1.

You will actually need to CREATE with the basic Information as such

-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
-- =============================================
-- Author:        Gary Hughes
-- Create date: 02/21/2014
-- Description:    Searches a string [@STRING] for a substring [@STRVAR]
--                based on starting position [@START] and wich
--                occurrance [@NTH].
-- =============================================
    -- Add the parameters for the function here
    @STRING nvarchar(max), -- String length up to 4000
    @STRVAR nvarchar(max), -- String length up to 4000
    @START smallint, -- 4000 max
    @NTH tinyint --31 max
RETURNS smallint
    -- Declare the return variable here
    DECLARE @IDX smallint
    SET @IDX = @START - 1
    -- Return the result of the function


Then Replace the CREATE in the workaround with ALTER