0

I am using Microsoft SQL Server with already stored data. In one of my tables I can find data like:

+--------+------------+
| id     | value      |
+--------+------------+
| 1      | 12-34      |
| 2      | 5678       |
| 3      | 1-23-4     |
+--------+------------+

I realized that the VALUE column was not properly formatted when inserted. What I am trying to achieve is to get id by given value:

SELECT d.id FROM data d WHERE d.value = '1234';

Is there any way to format data in column just before SELECT clause? Should I create new view and modify column in that view or maybe use complicated REGEX to get only digits (with LIKE comparator)?

  • P.S. I manage database in Jakarta EE project using Hibernate.
  • P.S.2. I am not able to modify stored data.
designuj
  • 127
  • 1
  • 2
  • 9
  • 1
    If `value` should be storing what looks like an integer value, why is it a `varchar`? If the data type was correct, you'd never be able to have a value like `'12-34'`. i would suggest fixing the data and the design, and then you can't store bad values. – Thom A Jan 24 '20 at 11:49
  • Sorry, financial and accountancy software usually works as somebody designed it about ten years ago, no posibbility to change this in this particular situation. – designuj Jan 24 '20 at 11:51
  • Financial and accounting application developers knew that numerical data should be stored in a numerical data type 10 years ago. I use one at the office that was designed in the 1970's and even *that* knows what a number is. And Accountants know far too well that `2` is less than `100` (but your application doesn't think that). – Thom A Jan 24 '20 at 11:54
  • Do not know if this can help: https://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string – VBoka Jan 24 '20 at 11:58

1 Answers1

2

One method is to use replace() before the comparison:

WHERE REPLACE(d.value, '-', '') = '1234'
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786