any way to simplify this LIKE wildcard expression in T-SQL, without resorting to CLR?

Question

I have a column of database names like so:

    testdb_20091118_124925 
    testdb_20091119_144925 
    testdb_20091119_145925
    ect...

Is there a more elegant way of returning only similar records then using this like expression:

select * from sys.databases where name 
LIKE 'testdb[_][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][_][0-9][0-9][0-9][0-9][0-9][0-9]'

Answer 1

No, it is not possible.

By the way, you need to put your underscore inside brackets because it means any character.

Answer 2

No, no "elegant" solution, I'm afraid.

Furthermore, introducing functions, whether "native" or CLR, in the WHERE clause would prevent SQL of using indexes to resolve the predicate (it would have to scan the whole table, unless some other predicate came to help, in parts)

A few things to notice:

• the use of the underscore may be acceptable here since the targeted values seem to follow a very regular pattern. However underscore when used with LIKE, is itself a wildcard (corresponding to one and exactly one character). If you truly want to specify underscore, "escape" them by putting them in brackets, i.e. 'abc[_]def' will match 'abc_def', precisely, but not 'abcXdef' for example.
• the expression could be made a bit more selective and shorter with things like

    'testdb_20[0-9][0-9][0-1][0-9][0-3][0-9][_][0-9][0-9][0-9][0-9][0-9][0-9]'

i.e. assuming dates will be in this century and limiting for day bigger than 3x etc.