Need to create a function in sql to find a string

Question

Hi guys, I am trying to write a function which can tell me whether there is something after second ; in the string or not.For example: the sample string is "2:00AM;3:00PM;". So the function in this case needs to return false.

Answer 1

Assuming that there'll always be a second ;, and no third one, this ought to work...

CREATE FUNCTION dbo.fn_Bob(@str VARCHAR(20)) RETURNS BIT
BEGIN
  RETURN CASE WHEN @str LIKE '%;' THEN 0 ELSE 1 END
END

Answer 2

The CHARINDEX has an optional parameter to choose the start position, so find the first index of the ";" and add 1 that will start at the next character and then check for the next index and the length should be longer.

DECLARE @stringToTest nvarchar(100)
SET @stringToTest = '2:00AM;3:00PM;';
IF(LEN(@stringToTest) > CHARINDEX(';', @stringToTest, CHARINDEX(';', @stringToTest) + 1))
 BEGIN
  PRINT 'Yes'
 END
ELSE
 BEGIN
  PRINT 'No'
 END

Answer 3

create function [dbo].[udf_IsValidCheck](@Value varchar(64)) returns bit
as
begin
    declare @IsValidCheck bit
    select @IsValidCheck = (case when charindex( ';', @Value, charindex(';', @Value) + 1) > 0 
                                            and charindex( ';', @Value, charindex(';', @Value) + 1) < len(@Value) then 1
                                    else 0 end)
    return @IsValidCheck
end

test data:

'2:00AM;3:00PM;' --returns 0

'2:00AM;3:00PM' --returns 0

'2:00AM;3:00PM;3rdValue;4thValue;' --returns 1

'2:00AM;3:00PM;3rdValue;' --returns 1

'2:00AM;3:00PM;3rdValue' --returns 1

'2:00AM;' -- returns 0

'2:00AM;' -- returns 0