SQL IF value LIKE

Question

I’m new to SQL, so apologies for what is probably a menial question.

I’m storing staff numbers in a format similar to ‘e01, e02 .. e98, e99’

I have a procedure to input values into the employee table that I only want to be carried out if the provided value for the staff number field is the same format as shown above.

I have tried ..

CREATE PROCEDURE new_staff (IN e_id employee ids,
                ….,
                ….,etc)
BEGIN
IF e_id not like 'e__'
   THEN /* fail*/

ELSE /* insert record*/
END

I tested this using the value ‘e9999’ and it didn't recognise the error, it instead just took the value 'e99'. Any help would be much appreciated.

Many thanks

-Edit- Sorry - To clear things up. I'm using SQL Anywhere and I want the maximum possible employee id to be e99. My logic was that using like e__ (two underscores) would make it impossible to input e100 or higher.

Answer 1

EDIT:

Try it the other way round

IF e_id like 'e__'  
   THEN 
   /* insert record*/  
ELSE   
  /* fail*/
END

If you want the wildcards to be only numbers, try this one:

IF e_id like 'e[0-9][0-9]'  
   THEN 
   /* insert record*/  
ELSE   
  /* fail*/
END

For further information on SQL-Wildcards visit this link.