SQL User Defined Function Generates a Non-Boolean Type Error

Question

I'm getting this error on a SQL user defined function:

An expression of non-boolean type specified in a context where a condition is expected, near ')'.

For this:

UPDATE LMI_Contact
SET Phone = NULL
WHERE dbo.LMI_IsSingleCharacterRepeated(Phone, '0')

where the function can be created using:

-- ***this will also find NULL and empty string values***
CREATE FUNCTION LMI_IsSingleCharacterRepeated (@string varchar(max), @char char(1))
RETURNS bit
AS 
BEGIN
    DECLARE @index int
    DECLARE @len int
    DECLARE @currentChar char(1)
    SET @index = 1
    SET @len= LEN(@string)

    WHILE @index <= @len
    BEGIN
        SET @currentChar = SUBSTRING(@string, @index, 1)
        IF @currentChar = @char
            SET @index= @index+ 1
        ELSE
            RETURN 0
    END
    RETURN 1
END;
GO

This function is for checking if a string is any specified single character, repeated.

caution - my function will also find fields with null values or an empty string, so really needs a little more work — Chris Halcrow, May 31 '13 at 06:56

score 31 · Accepted Answer · answered May 31 '13 at 06:06

31

You must use comparison operators against functions even if the return type is bit.

UPDATE LMI_Contact
SET Phone = NULL
WHERE dbo.LMI_IsSingleCharacterRepeated(Phone, '0') = 1

answered May 31 '13 at 06:06

Thomas

63,911
12
95
141

score 8 · Answer 2 · answered May 31 '13 at 06:00

8

Try This

CREATE FUNCTION LMI_IsSingleCharacterRepeated (@str varchar(max), @char char(1))
RETURNS BIT
AS 
BEGIN
    DECLARE @indx int
    DECLARE @len int
    DECLARE @currentChar char(1)
    SET @indx = 1
    SET @len= LEN(@str)

    WHILE @indx <= @len
    BEGIN
        SET @currentChar = SUBSTRING(@str, @indx, 1)
        IF @currentChar = @char
            SET @indx= @indx+ 1
        ELSE
            RETURN 0
    END
    RETURN 1
END;
GO

answered May 31 '13 at 06:00

Ravi

30,829
42
119
173

1

Thanks for the suggestion. I tried this, but boolean isn't a recognised data type. I'm using SQL Server 2008 R2 – Chris Halcrow May 31 '13 at 06:05
1

Updated again.. please check. – Ravi May 31 '13 at 06:06
2

Good thinking and thanks again. Use of reserved words in a query can easily catch a developer out. In my case, this wasn't the problem. I've upvoted the suggestion as useful :) – Chris Halcrow May 31 '13 at 06:59

score 5 · Answer 3 · answered May 31 '13 at 06:09

5

You need to modify the where clause of your query as:

UPDATE LMI_Contact
SET Phone = NULL
WHERE dbo.LMI_IsSingleCharacterRepeated(Phone, '0') = 1

answered May 31 '13 at 06:09

nikhil pinto

331
1
4
15

Thanks Nikhil - just beaten to the answer by Thomas, so I've given you an upvote – Chris Halcrow May 31 '13 at 06:58

SQL User Defined Function Generates a Non-Boolean Type Error

3 Answers3