SQL LIKE for partial sentence value

Question

I have a database (change) which I am trying to create an sql report on the detail field value. The issue is that the detail value displays a "phrase" and I need to evaluate based on this phrase, or a part of it.

SQL

SELECT *
FROM change
WHERE change.detail LIKE '%To: [Step Two]%'

I want it to display all of the values where detail contains "To: [1. Step Two]" but the result is consistently not returning anything, where there are table values for this. Following is an example of the full value of the detail field: "[Step] Changed From: [1. Step One] To: [1. Step Two]" The items in [] represent other values in the database as well

So the issues is that the full value to evaluate is between the quotes in the following "[Step] Changed From: [1. Step One] To: [1. Step Two]" I need to only display results which have the later part of the phrase "To: [1. Step Two]" and leave the initial being a multi char wildcard as the part "[Step] Changed From: [1. Step One]" could be a multitude of things. I feel that the brackets in the resulting value cause an issue when including them in the LIKE statement as [1. Step Two] is actually what is in the statement and needs to be evaluated. — user2755444, Sep 06 '13 at 20:12

Daniel Gimenez · Answer 1 · 2013-09-06T20:22:43.377

2

If this is Microsoft SQL Server then you can escape brackets:

SELECT *
FROM change
WHERE change.detail LIKE '%To: \[Step Two\]%' ESCAPE '\'

Brackets used LIKE clause stand for a character range, So [Step Two] matches a character after To: that is either S,t,E,p, ,w, or o.

You can read about this on MSDN.

edited Sep 06 '13 at 20:22

answered Sep 06 '13 at 20:00

Daniel Gimenez

18,530
3
50
70

score 0 · Accepted Answer · edited Nov 13 '18 at 23:42

Presuming you're using SQL Server/TSQL, the problem you have is that the square bracket characters have a special meaning:

[ ] Any single character within the specified range ([a-f]) or set ([abcdef]).

WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. In range searches, the characters included in the range may vary depending on the sorting rules of the collation.

In order to literally match the square bracket characters, you need to escape them:

LIKE '%To: \[Step Two\]%' ESCAPE '\'

Thank you this helped . . . my apologies for the poor explanation — user2755444, Sep 06 '13 at 20:17

score 0 · Answer 3 · edited Sep 06 '13 at 20:35

0

Part of the problem is the square brackets introduce character constants. Here is one solution:

where change.detail like '%To:  ___Step Two_%'

This just takes the risk that the square brackets are really needed for the match.

where change.detail like '%To:  __[[]Step Two]%'

An alternative is to escape those characters:

where change.detail like '%To:  __![Step Two!]%' escape '!'

edited Sep 06 '13 at 20:35

Sotirios Delimanolis

274,122
60
696
724

answered Sep 06 '13 at 20:07

Gordon Linoff

1,242,037
58
646
786

SQL LIKE for partial sentence value

3 Answers3