Split User Defined Function

  • kgayda

    SSCrazy

    Points: 2157

    Comments posted to this topic are about the item Split User Defined Function


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • wchaster

    Old Hand

    Points: 392

    When I ran the SELECT statement by itself I see that this is a function that exists, what is the change you are making to the default Function?

    I changed the function name and tried to create it and I receive an error.

    IF exists (SELECT * from dbo.sysobjects

    WHERE id = object_id(N'[dbo].[SplitOnSpace]')

    AND OBJECTPROPERTY(id, N'IsTableFunction') = 1)

    DROP FUNCTION [dbo].[SplitOnSpace]

    GO

    CREATE FUNCTION dbo.SplitOnSpace (

    @vcDelimitedString varchar(8000),

    @vcDelimiter varchar(1)

    )

    GO

    Msg 102, Level 15, State 1, Procedure SplitOnSpace, Line 4

    Incorrect syntax near ')'.

    The only change I made to your expamle was the object name.

    😀
    ACN is the world's largest direct seller of telecommunications and essential services.
    http://helpu.acndirect.com/
    ACN Digital Phone Service customers automatically enjoy unlimited calling to 60 landline
    destinations around the world, including India, Mexico and the UK!
    :hehe:

  • kgayda

    SSCrazy

    Points: 2157

    Actually, you did not include the function body. You would need to add the following for it to work:

    RETURNS @tblArray TABLE

    (

    ElementID smallint IDENTITY(1,1), --Array index

    Element varchar(1000) --Array element contents

    )

    AS

    BEGIN

    DECLARE

    @siIndex smallint,

    @siStart smallint,

    @siDelSize smallint

    SET @siDelSize = LEN(@vcDelimiter)

    --loop through source string and add elements to destination table array

    WHILE LEN(@vcDelimitedString) > 0

    BEGIN

    SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)

    IF @siIndex = 0

    BEGIN

    INSERT INTO @tblArray VALUES(@vcDelimitedString)

    BREAK

    END

    ELSE

    BEGIN

    INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))

    SET @siStart = @siIndex + @siDelSize

    SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)

    END

    END

    RETURN

    END

    GO


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • jamiepeele

    SSC Enthusiast

    Points: 169

    I tried to split a string on a space character and the function blew up

    select * from Split('one two', ' ')

  • jamiepeele

    SSC Enthusiast

    Points: 169

    some debugging and i found that when you len a blank string you get 0

    //returns 0

    select len(' ')

  • kgayda

    SSCrazy

    Points: 2157

    Just out of curiosity, why would you want to split on a blank string?


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • jamiepeele

    SSC Enthusiast

    Points: 169

    I want to split on spaces to basically get a list of words a user may enter in a search string.

    For example user may search "Blue Chevrolet Corvette"

    I then want to "full text search" format "Blue Chevrolet Corvette"

    into

    "Blue or Chevrolet or Corvette" "Blue near Chevrolet near Corvette"

    so i found your function and hooked it up then unit tested with a " " and it errored. anyway I added these lines right above your line

    --Custom code added by JP to split strings on blank strings

    --Todo: need futher testing on spliting with characters + ' ' 4ex split on 'abc '

    if len(@vcDelimiter) < 1

    begin

    SET @siDelSize = 1

    end

    else

    begin

    SET @siDelSize = LEN(@vcDelimiter)

    end

    --End custom code

    --loop through source string and add elements to destination table array

    WHILE LEN(@vcDelimitedString) > 0

    Although it doesn't work for all scenarios is does work for the way I want to use it. BTW, thanks for the function good stuff. and If you know of a better way to accomplish what I'm trying to do by all means let me know...thanks.

  • kgayda

    SSCrazy

    Points: 2157

    Sorry, I misread your original post to mean zero-length string not space.

    I am surprised I hadn't encountered that problem before so thanks for pointing it out. I have submitted an update that changes all the calls to LEN() to DATALENGTH(). That works fine since all of the characters are varchar and not nvarchar().


    Karen Gayda
    MCP, MCSD, MCDBA

    gaydaware.com

  • battula.praveen

    Old Hand

    Points: 390

    Hello,

    This is the exact solution you are looking for.

    Check out: T-SQL Split UDF function by delimiter

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply