CODEWITHSUNDEEP
sqlsql-servert-sqlcharindex
Kevin
Kevin

Reputation: 781

SQL Charindex vs LIKE operator

Can someone please explain me this weird behavior of the charindex function in SQL? We are searching the values where the second letter is an 'o'. I don't understand why query 1 and 3 don't return 'OO software ontwerp', while using the like operator in query 4 it does show up. Even when I'm searching the capitalized letter 'O' in query 5 the value 'OO software ontwerp' doesn't show.

DECLARE @T TABLE (Titel VARCHAR(255))
INSERT INTO @T VALUES
('Mobotu'),('OO software ontwerp'),('Compleet handboek Access 97'),('Compleet handboek Access 2000'),('Compleet handboek Access 95')

Query 1

SELECT titel FROM @T WHERE CHARINDEX('o', titel, 1) = 2 

  titel
-----------------------------
1 Mobotu
2 Compleet handboek Access 97
3 Compleet handboek Access 2000
4 Compleet handboek Access 95

Query 2

SELECT titel FROM @T WHERE CHARINDEX('o', titel, 1) = 1

  titel
-----------------------------
1 OO software ontwerp

Query 3

SELECT titel FROM @T WHERE CHARINDEX('o', LOWER(titel), 1) = 2

  titel
-----------------------------
1 Mobotu
2 Compleet handboek Access 97
3 Compleet handboek Access 2000
4 Compleet handboek Access 95

Query 4

SELECT titel FROM @T WHERE titel LIKE '_o%'

  titel
-----------------------------
1 Mobotu
2 OO software ontwerp
3 Compleet handboek Access 97
4 Compleet handboek Access 2000
5 Compleet handboek Access 95

Query 5

SELECT titel FROM @T WHERE CHARINDEX('O', titel, 1) = 2

  titel
-----------------------------
1 Mobotu
2 Compleet handboek Access 97
3 Compleet handboek Access 2000
4 Compleet handboek Access 95

Upvotes: 1

Views: 2968

Answers (1)

SqlZim
SqlZim

Reputation: 38063

charindex() finds the first occurrence, like matches a pattern, patindex() also matches a pattern, but still returns the first occurrence. You could use patindex('_o%',titel)=1

select titel 
from @t 
where patindex('_o%', titel) = 1

rextester demo: http://rextester.com/JCHFQT86136

returns

+-------------------------------+
|             titel             |
+-------------------------------+
| Mobotu                        |
| OO software ontwerp           |
| Compleet handboek Access 97   |
| Compleet handboek Access 2000 |
| Compleet handboek Access 95   |
+-------------------------------+

and 

select titel 
from @t 
where patindex('%e%', titel) = 6

returns:

+-------------------------------+
|             titel             |
+-------------------------------+
| Compleet handboek Access 97   |
| Compleet handboek Access 2000 |
| Compleet handboek Access 95   |
+-------------------------------+

Upvotes: 2

Related Questions