CODEWITHSUNDEEP
sqlif-statementsql-like
user2346576
user2346576

Reputation:

SQL IF value LIKE

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.

Upvotes: 3

Views: 47884

Answers (3)

ThatGuy
ThatGuy

Reputation: 228

Parse everything after 'e' and see if the value is <100. or Add a column that supports integers and one column for the letter prefix - assuming that the e prefix is code for something and that it could be something besides e. when returning the value just create a string with the prefix and add to it the string representation of your integer.

Upvotes: 0

Obl Tobl
Obl Tobl

Reputation: 5684

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.

Upvotes: 1

Milen
Milen

Reputation: 8877

Try using global symbols (e%): 

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

ELSE /* insert record*/
END

Upvotes: 0

Related Questions