Post reply

Splitting a msg field with delimiter and creating a function to separate the field to multiple lines

  • November 13, 2007 at 12:52 pm

    #69204

    I have a table that has a message field. The field has the following information

    Acctno: 1047 MSG: GRN1/GRN2/GRN3/HR/POSSE

    I need the information to be split out and put into a table on 5 individual lines. I found the following script on this site and it runs but I'm getting the following error when I try to select

    Select * from dbo.fnSplitMsg

    Server: Msg 208, Level 16, State 3, Line 1

    Invalid object name 'dbo.fnSplitMsg'.

    Both the function and the view table is located in the same database. Does anyone have any suggestions? Thanks

    drop function dbo.fnsplitmsg

    Use Flight_Log070830

    CREATE FUNCTION dbo.fnSplitMsg(

    @smsglist VARCHAR(8000) -- List of delimited msgs

    , @sDelimiter VARCHAR(8000) = '/' -- delimiter that separates msgs

    ) RETURNS @List TABLE

    (

    Id int identity(1,1),

    Acctno varchar (25),

    msg VARCHAR(8000)

    )

    BEGIN

    DECLARE @acctno varchar, @sMsg VARCHAR(8000)

    WHILE CHARINDEX(@sDelimiter,@smsglist,0) <> 0

    BEGIN

    SELECT

    @sMsg=RTRIM(LTRIM(SUBSTRING(@smsglist,1,CHARINDEX(@sDelimiter,@smsglist,0)-1))),

    @smsglist=RTRIM(LTRIM(SUBSTRING(@smsglist,CHARINDEX(@sDelimiter,@smsglist,0)+LEN(@sDelimiter),LEN(@smsglist))))

    IF LEN(@sMsg) > 0

    INSERT INTO @List SELECT @acctno, @smsg from v_HEADER_MSG h where h.acctno like @acctno and h.msg like @smsg

    END

    IF LEN(@smsglist) > 0

    INSERT INTO @List SELECT @acctno, @smsglist from v_HEADER_MSG h where convert(varchar,h.acctno) like convert(varchar,@acctno)

    and convert(varchar,h.msg) like @smsg-- Put the last msg in

    RETURN

    END

  • November 13, 2007 at 1:05 pm

    #750821

    fnSplitMsg is a function not a table

    try

    select fnSplitMsg([msgfield]) from table

  • November 13, 2007 at 1:16 pm

    #750824

    That doesn't work either.

    I tried

    select fnSplitMsg([msg]) from v_header_msg and now I

    get the message:

    Server: Msg 195, Level 15, State 10, Line 1

    'fnSplitMsg' is not a recognized function name.

    But I don't get any error messages when I create and execute the function.

    This is my first time at creating a function. What is the @list supposed to be in the 4th line? I don't understand this line.

    RETURNS @List TABLE

  • November 13, 2007 at 1:23 pm

    #750826

    That is what is being returned by the function. What version of SQL are you using?

    Can you post the link to the thread where you got this function.

  • November 13, 2007 at 1:44 pm

    #750829

    I'm usting SQL 2000 and heres the link. It was actually anothor site.

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92385

  • November 13, 2007 at 2:00 pm

    #750836

    siboyd07 (11/13/2007)

    select fnSplitMsg[msg])... /quote]

    Try using select dbo.fnSplitMsg(msg).

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • November 13, 2007 at 2:13 pm

    #750839

    siboyd07 (11/13/2007)

    But I don't get any error messages when I create and execute the function.

    This is my first time at creating a function. What is the @list supposed to be in the 4th line? I don't understand this line.

    RETURNS @List TABLE

    The output of the function will be a table with the columns specified within the () right below it.

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • November 13, 2007 at 2:57 pm

    #750853

    Correct syntax:

    DECLARE @smsglist VARCHAR(8000), @sDelimiter VARCHAR(8000) = '/'

    SET @smsglist = 'Part 1/Part 2/Part 3'

    SET @sDelimiter = '/'

    Select * from dbo.fnSplitMsg (@smsglist, @sDelimiter)

    _____________
    Code for TallyGenerator

  • November 14, 2007 at 8:22 am

    #751106

    I don't think this will work because the number of items after the \ isn't always the same. Items will be added at the end of the string so I need a loop to keep counting the \'s until there are no more.

  • November 14, 2007 at 8:34 am

    #751113

    I have tried the following combinations and still nothing is working. A couple of them will query ok but will not execute.

    select fnSplitMsg[msg])... /quote]

    -- gives me this message Line 1: Incorrect syntax near ')'.

    select fnSplitMsg([msg])... /quote]

    -- gives me this message 'fnSplitMsg' is not a recognized function name.

    select dbo.fnSplitMsg(msg) -- The command(s) completed successfully.

    -- not execute gives me these messages

    --Server: Msg 208, Level 16, State 1, Line 1

    --Invalid object name 'dbo.fnSplitMsg'.

    --Server: Msg 207, Level 16, State 1, Line 1

    --Invalid column name 'msg'.

    select dbo.fnSplitMsg(msg) from v_header_msg -- runs the query but gives

    -- Server: Msg 208, Level 16, State 1, Line 1

    --Invalid object name 'dbo.fnSplitMsg'.

    Any other suggestions?

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

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