create function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

create function

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2015-03-03 : 16:01:55

Hi All.

I have select to split FullName on LastName and FirstName column

select
Substring(FullName, 1,Charindex(',', FullName)-1) LName
,Substring(FullName, Charindex(',', FullName)+1, LEN(FullName)) FName
from Table1

Is it possible to create function based on that select? If yes. How it to do?

Thanks.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-03-03 : 19:12:45

[code]CREATE FUNCTION [dbo].[MyFunction]
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
select
Substring(FullName, 1,Charindex(',', FullName)-1) LName
,Substring(FullName, Charindex(',', FullName)+1, LEN(FullName)) FName
from Table1[/code]

Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824)

eugz
Posting Yak Master

210 Posts

Posted - 2015-03-04 : 09:36:35

Hi Bustaz Kool. Thanks for replay.

It gives me errors on RETURN statement.

Msg 102, Level 15, State 1, Procedure MyFunction, Line 5
Incorrect syntax near 'RETURNS'.
Msg 319, Level 15, State 1, Procedure MyFunction, Line 5
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Thanks.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-03-04 : 10:45:14

[code]CREATE FUNCTION [dbo].[MyFunction]()
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
select
Substring(FullName, 1,Charindex(',', FullName)-1) LName
,Substring(FullName, Charindex(',', FullName)+1, LEN(FullName)) FName
from Table1[/code]

Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824)

eugz
Posting Yak Master

210 Posts

Posted - 2015-03-04 : 12:03:23

Thanks. But it not that I expected.

Let me I describe more detail. In Table1 I have FullName column witch I try to split by select statement on LastName and FirstName columns. And as a result I would like to update that Table1 by add two more column LastName and FirstName. Is it possible?

Thanks.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-03-04 : 12:18:09

You asked for a function, and Bustaz gave you a function :) If you simply want to update the table, you don't necessarily have to have a function.

First, add the two columns in the table if they don't already exist.

ALTER TABLE Table1 ADD FirstName VARCHAR(64) NULL, LastName VARCHAR(64) NULL;

Now update the table like so:

UPDATE Table1 SET
LastName = Substring(FullName, 1,Charindex(',', FullName)-1),
FirstName = Substring(FullName, Charindex(',', FullName)+1, LEN(FullName));

eugz
Posting Yak Master

210 Posts

Posted - 2015-03-04 : 12:44:06

Hi James K. Thanks for replay.

I know how to update table manually. I would like to know if it possible to update table by create function using my SELECT.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-04 : 12:58:16

quote:
Originally posted by eugz

Hi James K. Thanks for replay.

I know how to update table manually. I would like to know if it possible to update table by create function using my SELECT.

Thanks.

The point is that you shouldn't use a user-defined function for this.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

eugz
Posting Yak Master

210 Posts

Posted - 2015-03-04 : 14:48:05

Hi tkizer.

That is exactly what I'm would like to know how it create in my case.

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-04 : 14:52:43

I'm not understanding how the previous answers supplied didn't answer your question.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

eugz
Posting Yak Master

210 Posts

Posted - 2015-03-04 : 15:03:06

Can you show me how to do it?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-04 : 15:37:19

Maybe we aren't understanding your question...

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-03-04 : 16:19:58

Perhaps this?

UPDATE t1 SET
   t1.LastName = f.LName,
   t1.FirstName = f.FName
FROM
   Table1 t1
   INNER JOIN [dbo].[MyFunction]() f ON f.FullName = t1.FullName;

You will have to modify Bustaz's function to return a third column - FullName.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources