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

Question

Post reply

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

siboyd07

Old Hand

Points: 351
More actions
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

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

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

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

fnSplitMsg is a function not a table

try

select fnSplitMsg([msgfield]) from table

siboyd07 Old Hand Points: 351 More actions · Answer 2

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

mrpolecat SSCertifiable Points: 7014 More actions · Answer 3

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.

siboyd07 Old Hand Points: 351 More actions · Answer 4

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

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

Greg Snidow SSCoach Points: 16108 More actions · Answer 5

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.

Greg Snidow SSCoach Points: 16108 More actions · Answer 6

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.

Sergiy SSC Guru Points: 110208 More actions · Answer 7

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

siboyd07 Old Hand Points: 351 More actions · Answer 8

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.

siboyd07 Old Hand Points: 351 More actions · Answer 9

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?