SQL Server Performance

Looking for (substring_index of MySQL's function)

Discussion in 'Analysis Services/Data Warehousing' started by abhi, Jul 2, 2003.

  1. abhi New Member

    Could you please let me know what is the substitute function of (substring_index of MySQL) in SQL Server?
    Note : I know it I can do using CharIndex. But I want this type of feature.Please let me know.
    Thanks!
    Abhi


    mysql> select substring_index('abhi,abcd,12,15',',',1);
    +------------------------------------------+
    | substring_index('abhi,abcd,12,15',',',1) |
    +------------------------------------------+
    | abhi |
    +------------------------------------------+
    1 row in set (0.00 sec)

    mysql> select substring_index('abhi,abcd,12,15',',',2);
    +------------------------------------------+
    | substring_index('abhi,abcd,12,15',',',2) |
    +------------------------------------------+
    | abhi,abcd |
    +------------------------------------------+
    1 row in set (0.00 sec)

    mysql> select substring_index('abhi,abcd,12,15',',',3);
    +------------------------------------------+
    | substring_index('abhi,abcd,12,15',',',3) |
    +------------------------------------------+
    | abhi,abcd,12 |
    +------------------------------------------+
    1 row in set (0.00 sec)
    mysql> select substring_index('abhi,abcd,12,15',',',-1);
    +-------------------------------------------+
    | substring_index('abhi,abcd,12,15',',',-1) |
    +-------------------------------------------+
    | 15 |
    +-------------------------------------------+
    1 row in set (0.00 sec)
    mysql> select substring_index('abhi,abcd,12,15',',',-2);
    +-------------------------------------------+
    | substring_index('abhi,abcd,12,15',',',-2) |
    +-------------------------------------------+
    | 12,15 |
    +-------------------------------------------+
    1 row in set (0.00 sec)
  2. Chappy New Member

    substring_index is a very useful function, but there is no MSSQL equivalent. You would have to write your own user defined function if you really needed this.

    For anyone who doesnt know, the func takes str, delim and count, and returns..

    if count > 0 --> the left part of the string before 'count' occurences of delim are found
    if count < 0 --> the right part of the string after abs(count) occurences of delim are found
  3. abhi New Member

    Thnaks for your info.Could you pls tell me how to write a function .or where
    where should I start writing function in sql server?
    Regards,
    Abhi
  4. bambola New Member

    If you need the result as a string a scalar function would do.


    CREATE FUNCTION substring_index(@string varchar(8000), @delimiter char(1), @count int)
    RETURNS varchar(8000)
    AS
    BEGIN

    DECLARE @result varchar(8000)

    /*
    in the function body, find the delimiter according to
    the @count (you can use CHARINDEX) then return the
    substring you need.
    */

    RETURN @result
    END
    Bambola.


  5. satya Moderator

    Abhi also refer to books online for basic information and syntax help as a first hand information.

    HTH

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  6. bambola New Member

    BTW, to call the function you need to specify the owner. in this case it could be
    dbo.substring_index(.....)

    Bambola.
  7. abhi New Member

    thanks! your your help.
  8. Omar Rodriguez New Member

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    create FUNCTION [dbo].fnSubstring_Index
    (
    @BaseString varchar(255),
    @caracter varchar(255),
    @pos tinyint
    ) RETURNS varchar(255)

    AS
    /* ****************************************************
    Description:
    EQuivalent a mysql substring_index---- ---- -----------

    Create by Omar Rodriguez Tineo, From Santiago, Dominican Republic.
    Please dont remove this if you like the function
    **************************************************** */
    BEGIN

    /*
    DECLARE @pos INT
    Declare @BaseString varchar(255)
    Declare @caracter varchar(255)
    */
    Declare @indice tinyint
    Declare @pos2 tinyint
    Declare @result varchar(255)

    set @pos2= 1
    set @indice = 0
    --set @BaseString='hola mudo sato bueno cinco seis siete'
    --set @pos = 2
    --set @caracter= ' '


    while @indice < @pos
    begin
    begin


    set @pos2 = CHARINDEX(@caracter,@BaseString,@pos2+1)
    -- print @pos2

    set @indice = @indice +1

    end


    if @indice = @pos
    begin
    set @result= left(@BaseString,@pos2)
    --print @result
    break
    end
    else
    continue

    end




    RETURN @result
    END
  9. Luis Martin Moderator

    Omar,
    Welcome to the forums!!.
    Even this thread is 8 years old, we appreciate your collaboration.:)

Share This Page