CODEWITHSUNDEEP
sqlsql-servert-sql
Zayfaya83
Zayfaya83

Reputation: 93

Where clause on numbers dont give expected results

I have the following sample data in table_a.value:

abcdef 10 / 20 / 30 adfadsf
adfadsf 1000 / 10,5 / 300.5 kjbkjj
adsfadsf 0.1 / 8000 / 0,0005 asdfdasf
adsfasdf dfkjaf dsaflkjadslf asdfasdf 100 / 10.5 dslfjalksdf 500
adfdasf 50 sdlfkja 1000 alfdkjasf 50.5
ajkfdha asfdjlas dslkfjsdf

I want to filter out with a where clause row 1 to 3 where it has the following condition:

 where table_a.value like '%number / number / number%'.

Expected results would be:

  abcdef 10 / 20 / 30 adfadsf
  adfadsf 1000 / 10,5 / 300.5 kjbkjj
  adsfadsf 0.1 / 8000 / 0,0005 asdfdasf

I tried to make it work with the following code:

Where table_a.value like '%[0-9.,] / [0-9.,] / [0-9.,]%'

However, this is not giving me the expected results. Could somebody guide me in the right direction?

Upvotes: 0

Views: 75

Answers (2)

Martin Smith
Martin Smith

Reputation: 453328

SQL Server does not expose any true regular expression functionality via TSQL. The pattern syntax does not have any sort of support for quantifiers.

In this case you could first use TRANSLATE to ensure that all characters in the set 0-9., are denoted as 0 and then use a couple of nested replaces to collapse down contiguous series of 0 to be represented as 12. (REPLACE(REPLACE(..., '0', '12'),'21',''))

The initial TRANSLATE ensures the input to that step can't have any other 1 or 2 characters.

Then check that the result is LIKE '%12 / 12 / 12%'

SELECT *
FROM   ( VALUES ('abcdef 10 / 20 / 30 adfadsf'),
                ('adfadsf 1000 / 10,5 / 300.5 kjbkjj'),
                ('adsfadsf 0.1 / 8000 / 0,0005 asdfdasf'),
                ('adsfasdf dfkjaf dsaflkjadslf asdfasdf 100 / 10.5 dslfjalksdf 500'),
                ('adfdasf 50 sdlfkja 1000 alfdkjasf 50.5'),
                ('ajkfdha asfdjlas dslkfjsdf') )V(Col)
WHERE  REPLACE(REPLACE(TRANSLATE(Col, '123456789.,', '00000000000'), '0', '12'), '21', '') LIKE '%12 / 12 / 12%'

(Fiddle)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

SQL Server is not very good at doing this. You might be able to simplify your problem. For instance, you can get the same rows just by looking for two slashes surrounded by spaces:

where a_value like '% / % / %'

You can ensure that the central component is a number and that there are numbers before and after the spaces:

where a_value like '%[0-9] / [0-9]% / [0-9]%' and
      a_value not like '%[0-9] / [0-9]%[^0-9]% / [0-9]%'

This is not 100% equivalent to what you want to do, but it might be sufficient for your purposes.

Upvotes: 1

Related Questions