SQL Pattern matching not giving the correct output

Question

I am trying to find a certain set of characters in a column from a datatable. I have tried the pattern that seems more logical to me (right below) but it doesn't seem to be doing the job. What I wish to achieve is a pattern where I have something like '["5"]', basically with: square brackets, quotation marks, any integer number, quotation marks, square brackets. The output I am getting is just empty, and I can't seem to undersand why. Besides this, I would like to update the records that do not follow this pattern to follow it. Does anyone have a solution for this?

To give you some context, here is the test table:

I want to achive only the last three records. Here is what I have tried:

SELECT ToJsonTestValue
FROM   Test
WHERE  ToJsonTestValue LIKE '["%"]'

and

 UPDATE dbo.Test
SET     ToJsonTestValue = '["'+ToJsonTestValue+'"]'
WHERE ToJsonTestValue LIKE '#';

Square brackets need special processing... learn.microsoft.com/en-us/sql/t-sql/language-elements/… — Arvo
– Arvo, Commented Nov 10, 2020 at 14:32
If this question is connected with your previous question, WHERE ISJSON(ToJsonTestValue) = 0 is also an option. — Zhorov
– Zhorov, Commented Nov 10, 2020 at 14:52

Thom A · Accepted Answer · 2020-11-10 14:37:21Z

3

You have a couple of problem here. Firstly you have the square brackets, which needs escaping. Then you also use % which is a multi character wildcard, however, it appears that you want a single character. It also appears that that character can only be an integer, so you might want to be more specific. Either of these should give you the result you want:

--Using single character wildcard:
SELECT *
FROM (VALUES('["1"]'),('["["1"]"]'))V(S)
WHERE V.S LIKE '[[]"_"[\]]' ESCAPE '\';

--Specifically requiring int:
SELECT *
FROM (VALUES('["1"]'),('["["1"]"]'))V(S)
WHERE V.S LIKE '[[]"[0-9]"[\]]' ESCAPE '\';

answered Nov 10, 2020 at 14:37

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Miguel Ferreira Over a year ago

Yep that will do the trick! Also, is it possible to also update the remaining records? I mean, instead of just selecting them, the ones who do not follow the intended pattern be updated to follow it?

Thom A Over a year ago

You can use that WHERE in an UPDATE, yes @MiguelFerreira . A WHERE doesn't care what type of statement it's in; SELECT,DELETE, UPDATE, etc.

Miguel Ferreira Over a year ago

What would you do for match any number of digits instead of only [0-9]? It's because I have been trying and not being able to solve this problem, the only way I did was by adding more [0-9] (with the "*" I wasn't able to), could you provide me an answer?

Thom A Over a year ago

That's a new question at this stage, @MiguelFerreira . Moving the goal posts after getting an answer is frowned upon. Just make sure you reference this question and show your new attempts.

Collectives™ on Stack Overflow

SQL Pattern matching not giving the correct output

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related