How to make in T-SQL LIKE number or empty string inside square brackets?

Question

Is it possible to have a LIKE clause with one character number or an empty string?

I have a field in which I will write a LIKE clause (as a string). I will apply it later with an expression in the WHERE clause: ... LIKE tableX.FormatField .... It must contain a number (a single character or an empty string).

Something like [0-9 ]. Where the space bar inside square brackets means an empty string.

I have a table in which I have a configuration for parameters - TblParam with field DataFormat. I have to validate a value from another table, TblValue, with field ValueToCheck. The validation is made by a query. The part for the validation looks like:

... WHERE TblValue.ValueToCheck LIKE TblParam.DataFormat ...

For the configuration value, I need an expression for one numeric character or an empty string. Something like [0-9'']. Because of the automatic nature of the check, I need a single expression (without AND OR OR operators) which can fit the query (see the example above). The same check is valid for other types of the checks, so I have to fit my check engine.

I am almost sure that I can not use [0-9''], but is there another suitable solution?

Actually, I have difficulty to validate a version string: 1.0.1.2 or 1.0.2. It can contain 2-3 dots (.) and numbers.

Answer 1

I am pretty sure it is not possible, as '' is not even a character.

select ascii(''); returns null.

'' = ' '; is true

'' is null; is false

If you want exactly 0-9 '' (and not ' '), then you do to something like this (in a more efficient way than like):

where col in ('1','2','3','4','5','6','7','9','0') or (col = '' and DATALENGTH(col) = 0)