SQL Function TO Analyze passed string

I haven't created many SQL Server function but I need one to do the following.

The function will be passed a string.
If the string is all spaces, or the string is null, return null
Otherwise return the string, trimmed on the left and the right

Seems simple but I don't have the right syntax.  Can anyone spot my error:

USE [JTSConversion]
GO
/****** Object:  UserDefinedFunction [dbo].[checkBlankString]    Script Date: 3/19/2018 3:05:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALter FUNCTION [dbo].[checkBlankString] (@passedString varchar (Max)) 
					RETURNS varchar(max)

AS
BEGIN
	-- Declare the return variable here
	return iIF(isnull(@passedstring)) or len(Ltrim(rtrim(@passedString)))=0,null,ltrim(rtrim(@passedstring)))


END

Open in new window

LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
isnull() needs 2 arguments you want to check 'is null'
return iIF( @passedstring is NULL or len(Ltrim(rtrim(@passedString)))=0,null,ltrim(rtrim(@passedstring)))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
ALTER FUNCTION [dbo].[checkBlankString]
(
    @passedString varchar (Max)
)
RETURNS varchar(max)
AS
BEGIN
    RETURN (NULLIF(LTRIM(RTRIM(@passedString)), ''))
END
1
Mark WillsTopic AdvisorCommented:
The other way is to simply check len < 1
 iIF( len(Ltrim(rtrim(@passedString)))< 1,null,ltrim(rtrim(@passedstring)))

Open in new window

or NULLIF() per Scott's answer. But the reason you had errors is ISNULL(@passedString) v @passedString IS NULL
0
mlcktmguyAuthor Commented:
Thanks to you both, for the correction to my attempt,  alternate solutions and an explanation of why mine didn't work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.