SQL Setting column name as function from string

Question

This is my code:

-- Create Function
if object_id('dbo.J_TRIM') is not null drop function dbo.J_TRIM
GO
CREATE FUNCTION dbo.J_TRIM(@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@string)) AS [@string]
END
GO

SELECT dbo.J_TRIM(Supplier) FROM Supplier_List

It all works apart from when I try to set the column name as the string by using AS [@string]. Is this possible in SQL, and if so, how do I fix my code?

This is a scalar-valued function. It doesn't have a column name, it has an (unnamed) return value. If you want to assign a column name, that has to be done in the outer SELECT. — Jeroen Mostert
– Jeroen Mostert, Commented Dec 7, 2016 at 11:15
Incidentally, a function like this will only give you grief. The optimizer is terrible with scalar-valued functions. You will get much better results either simply writing out LTRIM(RTRIM(...)) every time or (if reusing the logic actually pays off, like it does not here) rewriting the function as an inline table-valued function. Unlearn what you know from languages actually designed for programming. — Jeroen Mostert
– Jeroen Mostert, Commented Dec 7, 2016 at 11:20

Gordon Linoff · Accepted Answer · 2016-12-07 11:34:34Z

1

What is this function supposed to be doing? Functions return values, not values with names. So, you can just do:

CREATE FUNCTION dbo.J_TRIM(@string VARCHAR(MAX))
    RETURNS VARCHAR(MAX)
BEGIN
    RETURN LTRIM(RTRIM(@string));
END;
GO

When you call the function, then you can give it an alias:

SELECT dbo.J_TRIM(Supplier) as Supplier
FROM Supplier_List ;

answered Dec 7, 2016 at 11:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Setting column name as function from string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related