Can cursor be avoided using a table functioin?
-
Hi everyone,
A developer asked me to write a function to split a full name into first, middle, and last names. The goal is to transfer the full name from the source table into the corresponding three columns in the destination table. I wrote a table-valued function which returns a table of three columns.
To perform the data load we are using a cursor which reads the full name from a row of the source table, feeds it to the function, and takes the resulting three string variables to perform an insert to the destination table.
Is there a set-based solution to this? Because the function requires a string input, I can't think of a way to load it except by using the cursor to grab the source value into a variable. This works fine on our tables of <10,000 rows, but doesn't seem practical for large tables.
TIA,
Elliott
-
You need scalar function taking string as 1st parameter and name part as 2nd one.
Then it's gonna be like this:
Select dbo.NamePart(FullName, 'FirstName') as FirstName, dbo.NamePart(FullName, 'MiddleName') as MiddleName, dbo.NamePart(FullName, 'LastName') as LastName
FROM ...
_____________
Code for TallyGenerator -
What is the logic you used in your table-valued function? Try to use that within the select statement and avoid the function totally. Something like this...
declare @FullName varchar(60)
Set @Fullname = 'Kennedy, John F.'
Select charindex(',' ,@Fullname)
Select FirstName = Ltrim(Rtrim(Left(right(@Fullname, charindex(',' ,@Fullname)), len(right(@Fullname, charindex(',' ,@Fullname))) - 3)))
, MiddleInit = Ltrim(Right(@FullName, 3))
, LastName = Left(@FullName, charindex(',' ,@Fullname) - 1)
This is assuming this logic is applicable to your data.
-
Thanks Sergiy, Ronald
I agree that putting the logic right into the insert statement would be better. But I was asked to provide a function, and so I did.
Sergiy--I'm not quite clear on how the scalar function would be used in the insert statement. I guess I should have mentioned that the data load (insert) will be grabbing additional columns besides the sFullName column. So I'm hoping that the function can do the name split and data load on the fly along with the other columns. Of course, the cursor does this by loading everything into variables one row at a time.
-
Using the same logic and how Sergiy puts it...
Create Function dbo.fn_NamePart(@FullName varchar(60), @NameType tinyint)
returns varchar(30)
as
begin
Declare @Return varchar(30)
Select @Return = case @NameType
when 1 then Ltrim(Rtrim(Left(right(@Fullname, charindex(',' ,@Fullname)), len(right(@Fullname, charindex(',' ,@Fullname))) - 3)))
when 2 then Ltrim(Right(@FullName, 3))
when 3 then Left(@FullName, charindex(',' ,@Fullname) - 1)
end
return @Return
end
GO
Insert into YourTable
Select FirstName = dbo.fn_NamePart(sFullName, 1)
,MiddleInit = dbo.fn_NamePart(sFullName, 2)
,LastName = dbo.fn_NamePart(sFullName, 3)
, AnotherColumn, AndOtherColumns
From YourSourceTable
-
Ahhhh, the light comes on Thanks. I can't wait to try it.
Elliott
-
What do you do with names like Juliet Anne Belle Smith Jones?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff--
We don't think there's any fool-proof logic that SQL can follow to identify last names. Multiple middle or last names are likely to escape machine analysis. Complex logic (such as counting all single letter interior words as part of the middle name) could help, but probably never solve the problem.
Our goal is to let SQL do 95-99% of the work; humans will have to do the rest. Our simple logic is: 2 words (identified by a blank space) equals first and last. 3 or more words will be split into first, middle, and the remaining words will be assigned as a last name.
In case you are wondering, we receive the "full" name field through a commercial data feed necessary to our business. It's up to us to try our best to store it atomically.
Cheers,
Elliott
-
That's the perfect answer... too many folks try to do AI with SQL Server.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply