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?
 
Mark WillsConnect With a Mentor Topic 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
 
Scott PletcherConnect With a Mentor Senior DBACommented:
ALTER FUNCTION [dbo].[checkBlankString]
(
    @passedString varchar (Max)
)
RETURNS varchar(max)
AS
BEGIN
    RETURN (NULLIF(LTRIM(RTRIM(@passedString)), ''))
END
1
 
Mark WillsConnect With a Mentor Topic 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.