Posted by jknaty on 05/24/07 13:47

On May 23, 3:22 pm, Ed Murphy <emurph...@socal.rr.com> wrote:
> jknaty wrote:
> > I'm trying to create a function that splits up a column by spaces, and
> > I thought creating a function that finds the spaces with CHARINDEX and
> > then SUBSTRING on those values would an approach. I get an error
> > saying that the I have an Invalid column 'Course_Number'. Not sure
> > why but I am very new to User Defined Functions. Here is what I have
> > so far:
>
> Please don't multi-post (post to each of several newsgroups separately),
> but rather cross-post (post to all of them in a single go). But only do
> that if it's truly relevant to all of them.
>
> > SET @Ind1 = CHARINDEX(' ', Course_Number, 4)
> > SET @Ind2 = CHARINDEX(' ',Course_Number, (CHARINDEX(' ',
> > Course_Number, 4)+1))
>
> This is your problem. See microsoft.public.sqlserver for details.

I understand, amd I'm new to posting to groups and just really wanted
to be sure this got posted to the right group of people to help me
out. I'm still not sure what is wrong but what I did to make things a
bit more clean is break out the part that I really want into a
separate function:

CREATE FUNCTION CourseEvalBreakdown

(

@Course_Number char(20)

)

RETURNS @CourseTable TABLE

(

Course_Number CHAR(20),

Col CHAR(2),

Area CHAR(4),

Number CHAR(4),

Section CHAR(4)

)

AS

BEGIN

DECLARE

@Ind1 tinyint,

@Ind2 tinyint,

@Col char(2),

@Area char(4),

@Number char(4),

@Section char(4)



BEGIN

SET @Ind1 = CHARINDEX(' ', @Course_Number, 4)

SET @Ind2 = CHARINDEX(' ',@Course_Number, (CHARINDEX(' ',
@Course_Number, 4)+1))



SET @Col = SUBSTRING(@Course_Number, 1, 2)

SET @Area = UPPER(SUBSTRING(@Course_Number, 4, @Ind1-4))

SET @Number = UPPER(SUBSTRING(@Course_Number, @Ind1+1, (@Ind2-
@Ind1)-1))

SET @Section = SUBSTRING(@Course_Number, @Ind2+1, 3)



END



INSERT @CourseTable VALUES (UPPER(@Course_Number),@Col, @Area,
@Number, @Section)

RETURN

END

GO

This works and I get the one column that I pass in to the seperated
columns I'm looking for, but now I'm stuck trying to figure out how to
merge those columns into the row that I got the original column which
was passed to this function. I tried to create a select statement
where I call this function but I got stuck there. Then I thought
maybe I can create a function to create a new table with everything
I'm looking for calling this function where I need it and build the
table I'm looking for but again I'm stuck. Any ideas on either?

Thanks,
Jason

[Back to original message]