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)
Go to Top of Page

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.
Go to Top of Page

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)
Go to Top of Page

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.
Go to Top of Page

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));
Go to Top of Page

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.
Go to Top of Page

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/
Go to Top of Page

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.
Go to Top of Page

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/
Go to Top of Page

eugz
Posting Yak Master

210 Posts

Posted - 2015-03-04 : 15:03:06
Can you show me how to do it?

Thanks.
Go to Top of Page

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/
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -