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.
Add a comment
|
3 Answers
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
1 Comment
Will A
+1 That would be certainly be a lot cleaner and would dispel my assumption!
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
Comments
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
