Index matching patter to any digit

user_11832881 0
Viewed 34

Having in account my last question, another problem emerged: I also need to find a pattern for any number, with no limit of digits. I also made some changes: I now have a test table with a column containing only numbers and what I intend to do is to those records who do not match the pattern in the previous question, I want them to follow it again, but including numbers with more than one digit.

Just to provide more context, this is the initial state of the table:

enter image description here

What I tried was to simply add the " * ", like below:

UPDATE dbo.Test
SET     ToJsonTestValue = '["' + ToJsonTestValue + '"]'
WHERE ToJsonTestValue NOT LIKE '[[]"[0-9]*"[\]]' ESCAPE '\';

and at a first glance it seemed ok, in the first execution of the query, it just added the pattern I wanted. But when I executed the query a second time, in order to verify that those who already had the pattern would be ignored, the result was this:

enter image description here

I also tried without the " * ", which works fine but then again, it wont cover all cases (numbers with more than one digit). Without the " * " makes the one digit numbers with the pattern be ignored, which was good, but not sufficient for the task. This being said, is there a way to solve this stress? Thanks in advance.

sql-server
2 Answers

Try this:

DECLARE @Test table ( test_id int, ToJsonTestValue varchar(255) );
INSERT INTO @Test VALUES ( 43, '6' ), ( 44, '7' ), ( 45, '8' ), ( 46, '888' );

-- Update @Test --
UPDATE @Test
SET
    ToJsonTestValue = '["' + ToJsonTestValue + '"]'
WHERE 
    ToJsonTestValue NOT LIKE '!["%[0-9]%"!]' ESCAPE '!';

-- Update @Test a second time to test 'NOT LIKE' pattern is being matched --
UPDATE @Test
SET
    ToJsonTestValue = '["' + ToJsonTestValue + '"]'
WHERE 
    ToJsonTestValue NOT LIKE '!["%[0-9]%"!]' ESCAPE '!';

-- Show final resultset --
SELECT * FROM @Test ORDER BY test_id;

Returns

+---------+-----------------+
| test_id | ToJsonTestValue |
+---------+-----------------+
|      43 | ["6"]           |
|      44 | ["7"]           |
|      45 | ["8"]           |
|      46 | ["888"]         |
+---------+-----------------+
user_33913340

I believe this will work; it works for the examples but I have concerns we'll get a different one again.

You need to use an extra LIKE (specifically a NOT LIKE) and instead check to ensure that there are no other characters other than 0-9 between the brackets ([]):

SELECT *
FROM (VALUES('["1"]'),('["["1"]"]'),('["12"]'))V(S)
WHERE V.S LIKE '[[]"%"[\]]' ESCAPE '\'
  AND V.S NOT LIKE '[[]"%[^0-9]%"[\]]' ESCAPE '\';
user_20299830
Related