T-SQL CHARINDEX ve PATINDEX Fonsiyonları

Bu iki fonksiyon basit anlamda string içinde string aramak için kullanılır ve bulduğunda başlangıç indeksini dönürürler. Açıklamaya CHARINDEX fonksiyonuyla başlayalım. Kullanım yukarıda bahsettiğim gibidir. Kullanım şekli ve bazı kullanım detaylarından aşağıda bahsedeceğim.

Aldığı parametreler ve fonksiyon kullanmı aşağıdaki gibidir. Ve aramaya başlanacak pozisyonu girmek opsiyoneldir eğer başlangıç pozisyonu girmezsek default olarak aramaya bir numaralı karakterden başlanır.

CHARINDEX(cümlede aranan karakter dizisi , araştırılacak cümle , başlanacak pozisyon(opsiyonel))

Bir örnekle açıklayalım.

DECLARE @string VARCHAR(128);
SET @string = 'anormal bir duruma verilen anormal bir tepki, aslında normal bir tepkidir.'; --içerisinde arama yapacağımız cümle
--Bizde yukarıdaki cümlede anormal kelimesini arayacağız ayrıca sql'de stringlerin karakter index'i birden başlıyor
SELECT CHARINDEX('anormal',@string) AS 'Pozisyon1';--il kullanım şeklinde başlangıç pozisyonu vermedik
SELECT CHARINDEX('anormal',@string,2) AS 'Pozisyon2';--bu kullanımda ise 2 numaralı index aramaya başlangıç pozisyonu olarak verildi
SELECT CHARINDEX('anormal',@string,29) AS 'Pozisyon3';--sıfır döner çünkü 29'dan sonra anormal kelimesi geçmiyor.

Sonuçlar :

Pozisyon1
1

Pozisyon2
28

Pozisyon3
0

İlk iki parametreden herhangi biri null olduğunda sonuç null döner.

DECLARE @string VARCHAR(128);
SET @string = 'anormal bir duruma verilen anormal bir tepki, aslında normal bir tepkidir.';
SELECT CHARINDEX(NULL,@string) AS 'Pozisyon4';
SET @string = NULL;
SELECT CHARINDEX('anormal',@string) AS 'Pozisyon5';

Sonuçlar :

Pozisyon4
NULL

Pozisyon5
NULL

Yukarıdaki kullanımlar default olarak case insensitive olarak yapılmaktadır. Bu kullanıma büyük/küçük harf duyarlılığı kazandırmak için aşağıdaki şekilde kullanmalıyız.

DECLARE @string VARCHAR(128);
SET @string = 'anormal bir duruma verilen Anormal bir tepki, aslında normal bir tepkidir.';
SELECT CHARINDEX ('Anormal', @string COLLATE Latin1_General_CS_AS) AS 'Pozisyon6';

Sonuç :

Pozisyon6
28

Sonuçtan anlaşıldığı gibi ilk anormal kelimesi ‘Anormal’ kelimesi ile eşleşmiyor çünkü arama case sensitive olarak yapılıyor.

Gelelim PATINDEX Fonksiyonunun kullanımına. PATINDEX fonksiyonu string içinde string aramaktan ziyade verilen bir örüntüye uygun bir karakter dizisi yakalamak için kullanılır. Örüntü ise belirli bir kurala uygun karakter dizilerini yakalamada kullanılır. Örneğin ismi s ile başlayanlar yada ismi içinde içerisinde x geçen tarzında belirsiz string’leri örüntüler yazarak buluruz. PATINDEX(‘%pattern%’, expression) şeklinde kullanılır ve fonksiyonun dönen değeri int tipinde bir ifadedir. Ve bu dönen değer eşleşme pozisyonunu gösterir. CHARINDEX fonksiyonunun böyle bir yeteneği yoktur. Bir örnekle inceleyelim.

DECLARE @string VARCHAR(128)
SET @string = 'İnsan, güldüğü kadar insandır';

SELECT PATINDEX('%ü_k%', @string) AS 'index1',
PATINDEX('%ü%k%', @string) AS 'index2',
PATINDEX('%in%', @string) AS 'index3',
--CHARINDEX('in',@string) ile PATINDEX('%in%', @string) aynıdırlar
PATINDEX('%g[^i]%', @string) AS 'index4',
PATINDEX('%d[a-i]r%', @string) AS 'index5',
PATINDEX('%a[a-i]r%', @string) AS 'index6';

Sorgu çalıştığında aşağıdaki sonuç dönecek.

index1	index2	index3	index4	index5	index6
14	9	1	8	18	0

İndex1 olarak adlandırılan fonksiyonda _ ve % karakterleri örüntü oluşturmak için kullanılmış burada _ herhangi bir karakterle eşleşir. % karakteri ise burada ve diğer örüntülerde başa ve sona konularak aranan karakter serisinin cümlenin başında, sonunda veya ortasında olabilme ihtimallerinin bulunduğu belirtilir.

Üçüncü örüntüde ise ^ karakteri kullanılmış. Amacı ise kendisinden sonra gelen karakter dışında gelecek karakter dışındaki karakterleri kabul etmektir.

Son örüntüde kullanılarn [] bu karakterler ise karakter listesinde belirttiğiniz karakterler ile eşleşen sorgular hazırlamak için kullanılır. [a-i] ifadesi a ve i arasında a ve i dahil bir karakter ile eşleşme sağlar.

Sırasıyla eşleşen kısımlara bakalım : (eşleşmeler kırmızı yazılmıştır)

index1 : İnsan, güldüğü kadar insandır
index2 : İnsan, güldüğü kadar insandır
index3 : İnsan, güldüğü kadar insandır
index4 : İnsan, güldüğü kadar insandır
index5 : İnsan, güldüğü kadar insandır
index6 : İnsan, güldüğü kadar insandır(eşleşme yok)

PATINDEX’de Case sensitive değildir ve Case sensitive olması için şu şekilde kodlamalıyız.

DECLARE @string VARCHAR(128)
SET @string = 'insan, güldüğü kadar İnsandır';
SELECT PATINDEX('%insan%', @string) AS 'index7',
PATINDEX('%İnsan%', @string COLLATE Latin1_General_CS_AI)
AS 'index8'

index7 : insan, güldüğü kadar İnsandır
index8 : insan, güldüğü kadar İnsandır

Eşleşmeler bu şekilde gerçekleşir.