none
T-SQL Question (instr equivalent)

    Question

  • Does instr function work in SQL Server? or does it have an equivalent?

    //Example

    SELECT * FROM Opportunities where instr(1, OtherLeads, 'John Grove') > 0

     


    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Thursday, April 22, 2010 4:12 PM

Answers

  • I'm not that familiar with the PLSQL instr function, but this should do the same thing:

    SELECT * FROM Opportunities where PATINDEX('%John Grove%', OtherLeads) > 0

    The % are used as wildcards in PATINDEX and it will return the starting charachter index if the searched expression is found.


    Gulf Coast SQL

    • Marked as answer by JohnGrove Thursday, April 22, 2010 4:22 PM
    Thursday, April 22, 2010 4:21 PM
  • Yes - CHARINDEX:

    SELECT * FROM Opportunities where CHARINDEX('John Grove',OtherLeads) > 0

    • Marked as answer by JohnGrove Thursday, April 22, 2010 4:22 PM
    Thursday, April 22, 2010 4:21 PM

All replies

  • I'm not that familiar with the PLSQL instr function, but this should do the same thing:

    SELECT * FROM Opportunities where PATINDEX('%John Grove%', OtherLeads) > 0

    The % are used as wildcards in PATINDEX and it will return the starting charachter index if the searched expression is found.


    Gulf Coast SQL

    • Marked as answer by JohnGrove Thursday, April 22, 2010 4:22 PM
    Thursday, April 22, 2010 4:21 PM
  • Yes - CHARINDEX:

    SELECT * FROM Opportunities where CHARINDEX('John Grove',OtherLeads) > 0

    • Marked as answer by JohnGrove Thursday, April 22, 2010 4:22 PM
    Thursday, April 22, 2010 4:21 PM
  • Thanks
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Proposed as answer by Nishink Thursday, March 15, 2012 1:08 PM
    • Unproposed as answer by Nishink Thursday, March 15, 2012 1:08 PM
    Thursday, April 22, 2010 4:22 PM
  • instr in oracle gives the ability to find even second occurence (we can give the our choice for finding the occuurence) so do we have anything equivalent to that in sql server???

    can some one suggest anything


    nishin


    • Edited by Nishink Thursday, March 15, 2012 1:18 PM
    Thursday, March 15, 2012 1:11 PM
  • Only with the custom functions. Brad Schulz has them

    Handy String Functions


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, March 15, 2012 1:18 PM
  • i was looking for inbuilt function in sql server than custom ones..:)

    Thanks


    nishin

    Thursday, March 15, 2012 1:45 PM
  • I understand, but unfortunately SQL Server charindex and patindex functions are lacking this particular functionality. Charindex has 3rd optional parameter, but it's a start position, not occurrence number.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, March 15, 2012 1:58 PM
  • Great idea.  This will remove lot of coding for sql filter.
    Thursday, August 15, 2013 5:37 AM