CODEWITHSUNDEEP
firebirdfirebird2.5
kovarov
kovarov

Reputation: 792

list trigger no system ending with "_BI"

I want to list the trigger no system ending with "_BI" in firebird database, but no result with this

select * from  rdb$triggers
where 
rdb$trigger_source is not null 
and (coalesce(rdb$system_flag,0) = 0) 
and (rdb$trigger_source not  starting with 'CHECK' )
and (rdb$trigger_name like '%BI')

but with this syntaxs it gives me a "_bi" and "_BI0U" and "_BI0U" ending result

and (rdb$trigger_name like '%BI%')

but with this syntaxs it gives me null result and (rdb$trigger_name like '%@_BI')

thank you beforehand

Upvotes: 1

Views: 68

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 108941

The problem is that the Firebird system tables use CHAR(31) for object names, this means that they are padded with spaces up to the declared length. As a result, use of like '%BI') will not yield results, unless BI are the 30th and 31st character.

There are several solutions

For example you can trim the name before checking

trim(rdb$trigger_name) like '%BI'

or you can require that the name is followed by at least one space

rdb$trigger_name || ' ' like '%BI %'

On a related note, if you want to check if your trigger name ends in _BI, then you should also include the underscore in your condition. And as an underscore in like is a single character matcher, you need to escape it:

trim(rdb$trigger_name) like '%\_BI' escape '\'

Alternatively you could also try to use a regular expressions, as you won't need to trim or otherwise mangle the lefthand side of the expression:

rdb$trigger_name similar to '%\_BI[[:SPACE:]]*' escape '\'

Upvotes: 1

Related Questions