I've been learning SQL by myself, and I'm stumped on this one.
In this table I'm working on, I have a persons first name and last name as a column, and I'm using a user defined function to concat the two columns to get their full name.
What I have works for one column, but I don't know how to loop it. Here's my code.
CREATE FUNCTION dbo.fullName(
@firstName VARCHAR(10),
@lastName VARCHAR(10))
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @fullName VARCHAR(20)
SELECT @fullName = CONCAT(firstName, ' ', lastName) FROM stray_kids
RETURN @fullName;
END
Then when I call it with SELECT lastName, firstName,dbo.fullName(firstName, lastName) FROM stray_kids It comes up with the image I added, which is great for the first row and then I then tried to add a while loop and counters and such but couldn't figure it out. Also, how would I name the new column name?
Thanks if you can help! <3 my output
2条答案
按热度按时间8yparm6h1#
The functionality you are after is easily achieved using a computed column
Then you can simply
Newer versions of SQL Server also support
concat_ws(' ', FirstName, LastName);
If you really wanted to use a function, all you need is
Note the return type needs to account for the additional space character... 10 chars seems al ittle short though so perhaps use
varchar(50)
iovurdzv2#
To apply your dbo.fullName function to all rows in the stray_kids table, you can use the SELECT statement with the FROM clause to query the table and apply the function to each row:
This will create a new column named fullName in the result set that contains the full name for each person.
You don't need to use a loop or counters in this case because the SELECT statement already loops over all rows in the table.
But remember that using a function here is not an optimal solution. Use a simple concatenation.