Using a UDF to populate a series of 1's and 0's based on string

Question

I have a table in sql that has a column named [message]. I need to use a UDF to populate a 1 or a 0 dependant on if that substring contains 'LHL'.

My code:

    create function dbo.generate 
    (
    @result varchar(max)
    )
    returns int
    as
    begin
    declare @message varchar(max) = (Select [message] from POST)
    if (PATINDEX('%LHL%', @message) > 0)
    begin
    set @result = '1'
    end
    else 
    set @result = '0'
    return @result
    end
    go

    select dbo.generate('%LHL%')

With this I get the error:

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I just need a list that has 0's and 1's. I can generate a list using PATINDEX that can give me where the LHL begins, but cannot seem to change the number to a 1 (if exists) as shown. (also must use a UDF not a CTE)

    with cte as
    (select [message] from POSt
    ), 
    pos as
    (select patindex('%LHL%',[message]) pos, [message] from cte
    union all
    select pos+patindex('%LHL%',substring([message], pos+1, len([message]))) pos, [message] from pos
    where patindex('%LHL%',substring([message], pos+1, len([message])))>0
    )
    select pos from pos

d_luffy_de · Answer 1 · 2015-08-17T13:00:04.630

Why not use an EXISTS clause to check for the existence of rows??

IF(EXISTS(Select [message] from POST WHERE [message] LIKE '%LHL%'))
BEGIN
    SET @result = '1'
END
ELSE
BEGIN
    SET @result = '0'
END

    RETURN @result

Based on what you want to achieve (i.e To check if LHL exists) this is the best option. However, if you want all the positions by making it a string first convert your entire column data into a string and then search.

score 1 · Answer 2 · answered Aug 17 '15 at 06:52

1

SELECT statement declare @message varchar(max) = (Select [message] from POST) used to set value for variable @message is returning more than one value. Restrict your SELECT statement to return only one value at a time, so that it can be stored in '@message' variable.

answered Aug 17 '15 at 06:52

Himanshu gaur

151
6

You can also try calling your function for each row of 'POST' table. In this way you can pass one value from [message] column at a time. You can you '@message' variable with that particular value in your function and can return '1' or '0' for each row. – Himanshu gaur Aug 17 '15 at 06:56

Using a UDF to populate a series of 1's and 0's based on string

2 Answers2