Pattern matching in sql

[HunainHafeez]

I am receiving a Code in sms like "yyyy/mm-digit" (digit increments with every message). so i want to ensure that the code sent by user is exactly in that format otherwise another action. So how ?

Here is my code to check but it always execute else block, why ? Note: I want it for e.g. "2014/8-digit" kinda pattern , how ? (it changes with respect to year and month)

Here is my little effort but it's throwing dateitme conversion failed error plus it might have other issues too, Help

Alter PROCEDURE TestTrigger



AS
BEGIN
     Begin Try
           Declare @msg as varchar(20)
           SELECT @msg = '2014/9-1'

           DECLARE @yyyymm varchar(255) = Convert(Varchar ,(cast(year(@msg) as varchar(255)) + '/' +
                                right(cast(month(@msg) as varchar(255)), 2)
                               ));

                IF (@msg like @yyyymm + '-[0-9]%' and
                    @msg not like @yyyymm + '-%[^0-9]%'
                   )
                  BEGIN
                    Print 'Done'
                  END
                  ELSE
                  BEGIN
                    Print 'Not Done'
                  END
    END TRY
    BEGIN CATCH
     Select ERROR_MESSAGE() as ErrorMsg
    END CATCH

END
GO

[adam_k]

I'm not sure what you are trying to do, but try something like this:

Declare @msg as varchar(20)

SELECT @msg = '2014/9-1'

declare @slash as int 
declare @minus as int 

select  @slash = charindex('/', @msg), @minus = charindex('-',@msg)


DECLARE @yyyymm varchar(6) 

select @yyyymm = convert(varchar(4),left(@msg,@slash)) + convert(varchar(2),substring(@msg,@slash +1 ,@minus-@slash-1))

select @yyyymm

Please note:
1) I just typed this and didn't test it, some typos might be found.
2) It is advised that you check that charindex has returned values and that @minus is greater than @slash if you expect it that way.
3) It is advised that you check that year and month are actually integers before doing anything with them
4) I know that although I'm using dynamic positions to substring and left, I'm assigning the values to a fixed length var, a) but I don't remember if varchar(@slash-1) would work and I'm too bored to test it b) It would make things even more complex for you and finally c) I'm not gonna test it while @yyyymm is only 6 chars long.

Good luck