Geek Answers Handbook

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

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

I know this will sound stupid, and I'm pretty sure it is not possible, but I will ask.

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

Something like [0-9 ] . Where a space in square brackets means an empty string.

EDIT:

I think I’m not explaining it very well. I have a table in which I have a configuration of parameters - TblParam with a DataFormat field. I have to check the value from another TblValue table with the ValueToCheck field. Verification is performed upon request. The part to check is as follows:

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

For the configuration value, I need an expression for 1 numeric character or an empty string. Something like [0-9''] . Due to the automatic nature of the check, I need a single expression (without AND OR OR statements) that can match the query (see Example above). The same check is valid for other types of checks, so I need to install my check mechanism.

I'm pretty sure I can't use [0-9''] , but I ask the question if anyone has any other suitable solution.

Actually, it’s hard for me to check the version line: 1.0.1.2 or 1.0.2 . It can contain 2-3 dots ( . ) And numbers.

+5
source share
3 answers

It’s pretty accurate that this is not possible, because '' is not even a symbol

select ascii (''); returns null

'' = ''; truly

'' - null; false

if you want exactly 0-9 '' (not '')
then you do something like this
(more effective than like)
where col in (1, 2, 3, 4, 5, 6, 7, 9, 0)) = 0)

+1
source

This is a difficult question ... As far as I can tell, there is no way to do this with just one such sentence ... You need to do like '[0-9]' OR like '' . You could accomplish this by specifying the second column in your TableX This indicates either a second template or the inclusion of spaces.

+1
source

If I understand your question correctly, you need something that catches an empty string. Try using 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

Returns exactly three records: '', '1' and '2'.

Update: A more convenient method was found with only one sentence:

  select a from t1 where isnull(nullif(a,''),0) like '[0-9]'
+1
source

More articles:


All Articles