Search code examples
sqlfirebirdsql-like

Firebird Switch from "Similar to" to "Like"

I have created a domain using the "Similar to" function under firebird 2.5, but now I have to downgrade my database to Firebird 2.1, and it seems impossible beacause of that function. I want to find the way to express my domain with the "Like" function so that it runs in Firebird 2.1. Here is my domain code :

CREATE DOMAIN CODET
 AS varchar(16)
 NOT NULL
 CHECK (char_length(value)=16
 and
 value SIMILAR TO 
  '[G]{1}[C,M,E]{1} [1-6]{1} [E]{1}[P,S,L]{1} 
  [A-E]{1} [0-9]{1}[0-9]{1} [0-9]{1}[0-9]{1}[0-9]{1}'
  and substring(value from 14 for 3)>'000')
 COLLATE NONE;
Solution

  • You could try to "translate" it term by term, ie

    • the first one is [G]{1} which means that value must start with G so this becomes value STARTING WITH 'G'
    • [C,M,E]{1} - second character must be either C, M or E which is (value like '_C%')or(value like '_M%')or(value like '_E%')
    • [1-6]{1} - fourth character must be number between 1 and 6, including. So SUBSTRING(value FROM 4 FOR 1) BETWEEN '1' AND '6'

    and so on. And then combine them into one:

    ...
CHECK ( char_length(value)=16
   and value STARTING WITH 'G'
   and( (value like '_C%')or(value like '_M%')or(value like '_E%') )
   and SUBSTRING(value FROM 4 FOR 1) BETWEEN '1' AND '6'
   ...
)

    As your string is fixed length and spaces in it are in fixed position you can check for them in single term like so

    value like '__ _ __ _ __ ___'

    ie all non-space positions are marked with _ and space positions contain space character.

    Alternatively you could use some UDF which implements similar to like operation.