5

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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Bogdan Bogdanov
  • 1,707
  • 2
  • 20
  • 31

3 Answers3

1

That's a tricky one... As far as I can tell, there isn't a way to do it with only one like clause. You need to do like '[0-9]' OR like ''.

You could accomplish this by having a second column in your TableX. That indicates either a second pattern, or whether or not to include blanks.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Adam Martin
  • 1,188
  • 1
  • 11
  • 24
1

If I correctly understand your question, you need something that catches an empty string. Try to use the nullif() function:

create table t1 (a nvarchar(1))
insert t1(a) values('')
insert t1(a) values('1')
insert t1(a) values('2')
insert t1(a) values('a')
-- must select first three 
select a from t1 where a like '[0-9]' or nullif(a,'') is null

It returns exactly three records: '', '1' and '2'.

A more convenient method with only one range clause is:

 select a from t1 where isnull(nullif(a,''),0) like '[0-9]'
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
brainboost
  • 379
  • 2
  • 14
  • I like your updated construct and I vote up. I need single construct so this is not answer I need, but your where clause is good from my point of view. – Bogdan Bogdanov Sep 04 '15 at 22:06
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)
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
paparazzo
  • 44,497
  • 23
  • 105
  • 176
  • Yes, but my construction is `LIKE TblParam.DataFormat `. Your answer is ok, but I can not fit. In that case I will need several column - I try to find some common pattern for checking. If filter becomes very complex - it will be of no use. – Bogdan Bogdanov Sep 04 '15 at 21:56
  • Want and get are not the same thing. '' is null - it is not a character – paparazzo Sep 04 '15 at 22:00
  • I agree - empty sting is not a character. I just check if there is a solution, even I think there is not - in construct like that. – Bogdan Bogdanov Sep 04 '15 at 22:02