0

I have the next issue, there is table with column data (type - nvarchar), this column can contains any characters, I need selected only data which are numbers or range of numbers, but to ignore white spaces. For example:

declare @tmp table (data nvarchar(192), Result nvarchar(192));

insert into @tmp SELECT '123', '<- valid';
insert into @tmp SELECT ' 123 ', '<- valid';
insert into @tmp SELECT '123-123', '<- valid';
insert into @tmp SELECT ' 123 - 123 ', '<- valid';
insert into @tmp SELECT '123jmj', '<- invalid';
insert into @tmp SELECT '123,5441', '<- invalid';
insert into @tmp SELECT '123,yjyj', '<- invalid';

SELECT * FROM @tmp

The result dataset must contains only valid rows. I can try the next

SELECT * FROM @tmp WHERE data NOT LIKE '%[^0-9]%'

But I got only first row.

Sanprof
  • 369
  • 1
  • 6
  • 17
  • As the Regex-like functionality you can see is quite limited, how about some alternatives? Earlier versions (2005+) have the `ISNUMERIC() `function, and newer ones (2012+) you can use `TRY_PARSE()`. – Bridge Nov 26 '15 at 15:32
  • You can `LTRIM(RTRIM(data))`, which will give your 2nd row as well. For the range you'll have to append an `OR` condition to your `WHERE`, it shouldn't be too difficult, but you may have to `REPLACE` the white spaces within the value. – HoneyBadger Nov 26 '15 at 15:32
  • 1
    @Bridge, `ISNUMERIC` is very unreliable, monetary signs and arithmetic signs also evaluate to true. `TRY_PARSE` won't work for the ranges. – HoneyBadger Nov 26 '15 at 15:33
  • @HoneyBadger is right - you need to handle the range of numbers yourself, I missed that requirement! Currently the Regex solution doesn't handle that either though :) – Bridge Nov 26 '15 at 15:34
  • And none of the solutions currently proposed work for non-latin numeric characters either, I suspect. – Bridge Nov 26 '15 at 15:36
  • @Bridge what are `non-latin numeric characters` ? – Juan Carlos Oropeza Nov 26 '15 at 15:37
  • You can have a look at this [question] http://stackoverflow.com/questions/21378193/regex-pattern-inside-sql-replace-function – BSergei Nov 26 '15 at 15:38
  • @JuanCarlosOropeza - [here's a list](http://www.fileformat.info/info/unicode/category/Nd/list.htm), what you consider numberic characters are not the same everywhere in the world. Not sure if I got the name right, I meant anything other than 0-9 - depends on OP's requirements really. – Bridge Nov 26 '15 at 15:45
  • 2
    T-SQL doesn't support regular expressions at all - those are wildcards, not regexes. You could use SQL CLR to use .NETs `Regex` class if the performance is acceptable to you (it should be, since `like` like this wouldn't use indices anyway) and if you can enable SQL CLR in the first place :) – Luaan Nov 26 '15 at 16:00

1 Answers1

2

First replace the white spaces with empty string

Next replace the - with .

Third use ParseName trick to separate the data.

After the separation you can filter the records using NOT LIKE '%[^0-9]%'

SELECT data
FROM   (SELECT *,
               Parsename(Replace(Replace(data, ' ', ''), '-', '.'), 2) st,
               Parsename(Replace(Replace(data, ' ', ''), '-', '.'), 1) ed
        FROM   @tmp) a
WHERE  st NOT LIKE '%[^0-9]%'
        OR ed NOT LIKE '%[^0-9]%'
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172