SUBSTRING in User Defined Function - Invalid column

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:

CREATE FUNCTION CourseEvalBreakdown

(

@fskey int

)

RETURNS @CourseTable TABLE

(

Col CHAR(2),

Area CHAR(4),

Number CHAR(4),

Section CHAR(4),

Term CHAR(3)

)

AS

BEGIN

DECLARE

@Ind1 tinyint,

@Ind2 tinyint,

@Rows int

DECLARE @crstbl TABLE (FStaffKey int

, Course_Number char(20)

, Term char(3)

, Col char(2)

, Area char(4)

, Number char(4)

, Section char(3)

)

INSERT INTO @crstbl (FStaffKey, Course_Number, Term)

SELECT FStaffKey, Course_Number, Term

FROM Eval

WHERE FStaffKey = @fskey

SET @Rows = @@rowcount

WHILE @Rows > 0

BEGIN

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

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



UPDATE @crstbl

SET Col = SUBSTRING(Course_Number, 1, 2)

WHERE FStaffKey = @fskey



UPDATE @crstbl

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

WHERE FStaffKey = @fskey



UPDATE @crstbl

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

WHERE FStaffKey = @fskey



UPDATE @crstbl

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

WHERE FStaffKey = @fskey

END



INSERT @CourseTable

SELECT Col, Area, Number, Section, Term FROM @crstbl

RETURN

END

GO

Re: SUBSTRING in User Defined Function - Invalid column

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.

Re: SUBSTRING in User Defined Function - Invalid column

On May 23, 3:22 pm, Ed Murphy 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

Re: SUBSTRING in User Defined Function - Invalid column

jknaty wrote:

> On May 23, 3:22 pm, Ed Murphy wrote:
>> jknaty wrote:

>>> 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:

What's wrong is that you're thinking in terms of operating on one row
at a time. You came awfully close to using cursors, the usual hallmark
of that particular mistake.

> CREATE FUNCTION CourseEvalBreakdown
>
> (
>
> @Course_Number char(20)
>
> )

And double-spacing everything. Makes it hard to read.

> 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?

What you really want is the following:

CREATE FUNCTION CourseEvalBreakdown
(
@fskey int
)
RETURNS @CourseTable TABLE
(
Col CHAR(2),
Area CHAR(4),
Number CHAR(4),
Section CHAR(4),
Term CHAR(3)
)
AS
BEGIN

DECLARE #CourseTableTemp TABLE (
FStaffKey int
, Course_Number char(20)
, Ind1 int
, Ind2 int
, Term char(3)
, Col char(2)
, Area char(4)
, Number char(4)
, Section char(3)
)

INSERT INTO #CourseTableTemp (FStaffKey, Course_Number, Term)
SELECT FStaffKey, Course_Number, Term
FROM Eval
WHERE FStaffKey = @fskey

UPDATE #CourseTableTemp
SET Ind1 = CHARINDEX(' ', Course_Number, 4)

UPDATE #CourseTableTemp
SET Ind2 = CHARINDEX(' ', Course_Number, Ind1 + 1)

UPDATE #CourseTableTemp
SET Col = SUBSTRING(Course_Number, 1, 2)
, Area = UPPER(SUBSTRING(Course_Number, 4, @Ind1-4))
, Number = UPPER(SUBSTRING(Course_Number, @Ind1+1, @Ind2-@Ind1-1))
, Section = SUBSTRING(Course_Number, @Ind2+1, 3)

INSERT INTO @CourseTable
SELECT Col, Area, Number, Section, Term
FROM #CourseTableTemp

RETURN
END
GO

Re: SUBSTRING in User Defined Function - Invalid column

This is great, and looks like what I'm looking for. But I tried it
and I keep getting "Cannot access temporary tables from within a
function". Should I create the table first and then drop it when the
function is done?

> What you really want is the following:
>
> CREATE FUNCTION CourseEvalBreakdown
> (
> @fskey int
> )
> RETURNS @CourseTable TABLE
> (
> Col CHAR(2),
> Area CHAR(4),
> Number CHAR(4),
> Section CHAR(4),
> Term CHAR(3)
> )
> AS
> BEGIN
>
> DECLARE #CourseTableTemp TABLE (
> FStaffKey int
> , Course_Number char(20)
> , Ind1 int
> , Ind2 int
> , Term char(3)
> , Col char(2)
> , Area char(4)
> , Number char(4)
> , Section char(3)
> )
>
> INSERT INTO #CourseTableTemp (FStaffKey, Course_Number, Term)
> SELECT FStaffKey, Course_Number, Term
> FROM Eval
> WHERE FStaffKey = @fskey
>
> UPDATE #CourseTableTemp
> SET Ind1 = CHARINDEX(' ', Course_Number, 4)
>
> UPDATE #CourseTableTemp
> SET Ind2 = CHARINDEX(' ', Course_Number, Ind1 + 1)
>
> UPDATE #CourseTableTemp
> SET Col = SUBSTRING(Course_Number, 1, 2)
> , Area = UPPER(SUBSTRING(Course_Number, 4, @Ind1-4))
> , Number = UPPER(SUBSTRING(Course_Number, @Ind1+1, @Ind2-@Ind1-1))
> , Section = SUBSTRING(Course_Number, @Ind2+1, 3)
>
> INSERT INTO @CourseTable
> SELECT Col, Area, Number, Section, Term
> FROM #CourseTableTemp
>
> RETURN
> END
> GO

Re: SUBSTRING in User Defined Function - Invalid column

jknaty wrote:

> This is great, and looks like what I'm looking for. But I tried it
> and I keep getting "Cannot access temporary tables from within a
> function". Should I create the table first and then drop it when the
> function is done?

Ah, I haven't actually used functions, only stored procedures. You
could either create-and-drop or switch to a SP, I suppose. Or you
could flatten the logic, which would work, but would be ugly and
easy to screw up during maintenance:

insert into @CourseTable (Col, Area, Number, Section, Term)
select
substring(Course_Number,1,2),
upper(substring(Course_Number,4,charindex(' ',Course_Number,4)-4)),
-- etc.

Or you could create a series of smaller UDFs that operate on one
value at a time:

create function CInd1 (@Course_Number char(20))
returns @Ind1 int as
begin
select @Ind1 = charindex(' ',@Course_Number,4)
return
end

create function CInd2 (@Course_Number char(20))
returns @Ind2 int as
begin
declare @Ind1 int
select @Ind1 = CInd1(@Course_Number)
select @Ind2 = charindex(' ',@Course_Number,@Ind1+1)
return
end

create function CCol (@Course_Number char(20))
returns @Col char(2) as
begin
select @Col = substring(@Course_Number,1,2)
return
end

create function CArea (@Course_Number char(20))
returns @Area char(4) as
begin
declare @Ind1 int
select @Ind1 = CourseInd1(@Course_Number)
select @Area = upper(substring(@Course_Number,4,@Ind1-4))
return
end

create function CNumber (@Course_Number char(20))
returns @Number char(4) as
begin
declare @Ind1 int
declare @Ind2 int
select @Ind1 = CourseInd1(@Course_Number)
select @Ind2 = CourseInd2(@Course_Number)
select @Number = upper(
substring(@Course_Number,@Ind1+1,@Ind2-@Ind1-1)
)
return
end

create function CSection (@Course_Number char(20))
returns @Section char(4) as
begin
declare @Ind2 int
select @Ind2 = CourseInd2(@Course_Number)
select @Section = upper(substring(@Course_Number,@Ind2+1,3))
return
end

create function CourseEvalBreakdown (@fskey int)
returns @CourseTable table (
Col char(2),
Area char(4),
Number char(4),
Section char(4),
Term char(3)
) as
begin
insert into @CourseTable (Col,Area,Number,Section,Term)
select CCol(Course_Number),
CArea(Course_Number),
CNumber(Course_Number),
CSection(Course_Number),
Term
from Eval
where FStaffKey = @fskey
return
end

Re: SUBSTRING in User Defined Function - Invalid column

jknaty (kumpfjn@gmail.com) writes:
> This is great, and looks like what I'm looking for. But I tried it
> and I keep getting "Cannot access temporary tables from within a
> function". Should I create the table first and then drop it when the
> function is done?

Just change:

>> DECLARE #CourseTableTemp TABLE (

To

>> DECLARE @CourseTableTemp TABLE (

And use @CourseTableTemp throughout the function.

Logically, temp tables and table variables are very similar, but there
is some fine-print which can be really confusing.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: SUBSTRING in User Defined Function - Invalid column

On May 24, 5:39 pm, Erland Sommarskog wrote:
> Just change:
>
> >> DECLARE #CourseTableTemp TABLE (
>
> To
>
> >> DECLARE @CourseTableTemp TABLE (
>
> And use @CourseTableTemp throughout the function.

Sure enough, that worked! Thanks again for everyone's help this is
just what I needed. I do have a SP problem but I'll create a new post
for that.

>
> Logically, temp tables and table variables are very similar, but there
> is some fine-print which can be really confusing.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

This is good to know, thanks!