Veriler üzerinde LIKE ve PATINDEX operatörlerini kullanarak string manipülasyonları


Warning: count(): Parameter must be an array or an object that implements Countable in /home/kazimce1/public_html/wp-content/plugins/microkids-related-posts/microkids-related-posts.php on line 645

Bu makale Doron Farber, tarafından 28 Mart 2011 tarihinde yazılmıştır. Ziyaretçilerime faydası olacağını düşündüğüm için yazının çevirisini yaptım.

Yazının amacı veri tabanında farklı formatlarda oluşturulmuş tarih (Date and Time) kolonlarını içerisinde yer alan verileri daha genel bir formatta (YYYMMDD) düzenleyip veri temizliği yapmaktır. Farklı tarih formatlarına ait temizlik için kullandığımız örnek veriler aşağıdaki gibidir.

CREATE TABLE #T     
    (       
       D1 [VARCHAR] (20) NULL    
    )
INSERT INTO #T VALUES('5/15/2006 18:11')
INSERT INTO #T VALUES('5/13/2006 17:24')
INSERT INTO #T VALUES('2007-02-07 11:38:07')
INSERT INTO #T VALUES('9/4/2008 6:55 19a')
INSERT INTO #T VALUES('5/11/2006 0:47:52')
INSERT INTO #T VALUES('2007-01-04 12:43:00')
INSERT INTO #T VALUES('')
INSERT INTO #T VALUES('  6/11/2006 0:47:52')
INSERT INTO #T VALUES('9/22/2002 07:20:07')
INSERT INTO #T VALUES('09/22/2002 07:20:07')
INSERT INTO #T VALUES('4/13/2007')
INSERT INTO #T VALUES('2/1/2007 20:23') 
INSERT INTO #T VALUES('04-21-2011 18:47:42')
INSERT INTO #T VALUES('04-21-2008 17:11:00')
INSERT INTO #T VALUES('2/1/2007 11:44') 
INSERT INTO #T VALUES('5/9/2007 9:22')
INSERT INTO #T VALUES('04/13/2007')
INSERT INTO #T VALUES('')
INSERT INTO #T VALUES('2004-04-21 16:00:00')
INSERT INTO #T VALUES('04-21-2008 17:11:00')
INSERT INTO #T VALUES('2004')
INSERT INTO #T VALUES('2010-04-21 16:00:00')
INSERT INTO #T VALUES('0000-04-21 16:00:00')
INSERT INTO #T VALUES('2011')
INSERT INTO #T VALUES('2004')
INSERT INTO #T VALUES('1700')
INSERT INTO #T VALUES('1800')
INSERT INTO #T VALUES('Nov 2 2010')
INSERT INTO #T VALUES('02-22-03 00:00:00')
INSERT INTO #T VALUES('02-22-03 00:00:00')
INSERT INTO #T VALUES('10/5/1941 8/2/2005')
INSERT INTO #T VALUES('30.09.2002')
INSERT INTO #T VALUES('05:05:06' ) 

ALTER TABLE #T ADD [D2] VARCHAR(20) 
ALTER TABLE #T ADD [Update_Type] TinyInt  

SELECT * FROM #T

Yazı boyunca kullanacağımız 4 patern bulunmaktadır, her pattern tarih formatını YYYYMMDD şeklinde düzenlemize yardımcı olacaktır. Bu işlem sonucunda örneğin 9/19/2009 şeklindeki verimiz 20090919 şeklinde formatlanacaktır. Yukarıda oluşturduğumuz örnek veriler, veri aktarma (import data) durumlarında veri temizliği yaparken gerçek hayatta karşılaşacağımız durumlara örnek teşkil etmektedir. Üzerinde patternleri deneyeceğimiz tarih formatları aşağıdaki gibidir :

1. 9/19/2009
2. 1/1/2009
3. 09/25/2010
4. 2009-01-09

Örneğin bu 9/19/2009 formattaki veriyi YYYYMMDD şekline dönüştürmek için kullanacağımız patern aşağıdaki gibi olacaktır.

SELECT    
    D1
FROM  #T  
WHERE D1 <> '' AND D1 LIKE '%[0-9][/][0-9][0-9][/][0-9][0-9][0-9][0-9]%'

LIKE operatöründe kullanılan ‘%’ yüzdeye wildcard karakter denmektedir. Bu wildcard karakterini LIKE operatöründe sadece sol yada sadece sağ tarafa koymak istenmeyen sonuçlar oluşturabilir. Bu yüzden string pattern’inde kullanacağız wildcard karakterine herhangi bir süprizle karşılaşmamak için dikkat etmeniz gerekmektedir. Verilerinizi filtrelemeye başlamadan önce boşlukları trim etmeniyi kural edininiz. (Ayrıca veri aktarım (data import) işlemleri için SQL Server Integration Services kullanabilirsiniz.)

--5/15/2006 18:11
--5/13/2006 17:24
--5/11/2006 0:47:52
--6/11/2006 0:47:52
--9/22/2002 07:20:07
--09/22/2002 07:20:07
--4/13/2007
--04/13/2007

1. Verilerden 9/19/2009 formatındaki verileri elde etmek için kullanacağımız patern :

SELECT    
    D1
FROM #T  
WHERE D1 <> '' AND D1 LIKE '[0-9][/][0-9][0-9][/][0-9][0-9][0-9][0-9]%'

Çalıştırıldığında aşağıdaki gibi belirttiğimiz formata uygun verileri elde ettiğimizi gözlemleyebiliriz.

--5/15/2006 18:11
--5/13/2006 17:24
--5/11/2006 0:47:52
--9/22/2002 07:20:07
--4/13/2007

2. Verilerden 1/1/2009 formatındaki verileri elde etmek için kullanacağımız patern :

SELECT    
    D1
FROM #T  
WHERE D1 <> '' AND D1 LIKE '[0-9][/][0-9][/][0-9][0-9][0-9][0-9]%'

Sonuç :

--9/4/2008 00:38:45
--2/1/2007 20:23
--2/1/2007 11:44
--5/9/2007 9:22 

3. Verilerden 09/25/2010 formatındaki verileri elde etmek için kullanacağımız patern :

SELECT    
    D1
FROM #T  
WHERE D1 <> '' AND D1 LIKE '[0-9][0-9][/][0-9][0-9][/][0-9][0-9][0-9][0-9]%'

Sonuç :

--09/22/2002 07:20:07
--04/13/2007

4. Verilerden 2009-01-09 formatındaki verileri elde etmek için kullanacağımız patern :

SELECT    
    D1
FROM #T 
WHERE D1 <> '' AND D1 LIKE '[0-9][0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9]%'

Sonuç :

--2007-02-07 11:38:07
--2007-01-04 12:43:00
--2004-04-21 16:00:00 

Yukarıda uyguladığımız her patern sonuç olarak beklediğimiz formattaki veriyi sorgu sonucunda elde etti. Gerçek tablo üzerinde böyle bir sorgu işlemi yaparken çalıştığımız kolonlarda index olduğunda sorgu işlemlerini daha hızlı yapabileceğimizi belirtmek isterim. Index tanımladıktan sonra aşağıdaki gibi bir sorgu çok daha hızlı çalışacaktır.

SELECT    
   D1,    
   REPLACE(LEFT(D1,10),'-','') AS ResultDate
FROM #T 
WHERE D1 <> '' AND D1 LIKE '[0-9][0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9]%'

Yukarıdaki sorguda bahsettiğimiz patternlere ek olarak 2009-01-09 formatındaki verileri işlemek için kullanabilirsiniz.

--2007-02-07 11:38:07  20070207
--2007-01-04 12:43:00  20070104
--2004-04-21 16:00:00  20040421 

Bu paternlere ek olarak işinize yarayacak patternleri buradan bulabilirsiniz.

Veri temizliği için oluşturduğumuz tabloya yeni bir kolon ekleyip, formatını güncellediğimiz verileri bu kolona (D2) güncelleyerek yazalım. Böylece önceki kolondaki (D1) formatın istediğimiz yeni formata (YYYYMMDD) uygun olarak güncellendiğini gözlemleyebilelim. Ayrıca hangi verilerimizin hangi patern’e göre güncellendiğinide başka bir kolonda (Update_Type) görelim. Bu formatlar dışında gerçekte uygulamalarda en azından 20 farklı formatta veri ile karşılaşabilirsiniz böyle durumlarda yazının başında da belirttiğimiz gibi daha genel bir formata (YYYYMMDD) çevirim işlemi yapabilmeliyiz. Buna örnek olarak aşağıdaki gibi tek bir script ile bu işlemi gerçekleştirebiliriz.

UPDATE #T
 SET D2 = CASE
      WHEN D1 LIKE '[0-9][/][0-9][0-9][/][0-9][0-9][0-9][0-9]%' THEN
         SUBSTRING(D1,6,4) + '0' + REPLACE(LEFT(D1,5),'/','')               -- 9/19/2009
      WHEN D1 LIKE '[0-9][/][0-9][/][0-9][0-9][0-9][0-9]%' THEN
         SUBSTRING(D1,5,4) + '0' + LEFT(D1,1) + '0' + SUBSTRING(D1,3,1)     -- 9/9/2009
      WHEN D1 LIKE '[0-9][0-9][/][0-9][0-9][/][0-9][0-9][0-9][0-9]%' THEN
         SUBSTRING(D1,7,4) + REPLACE(LEFT(D1,5),'/','')                     -- 09/19/2009
      WHEN D1 LIKE '[0-9][0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9]%' THEN
         REPLACE(LEFT(D1,10),'-','')                                        -- 2009-01-09
      ELSE D2 END,     
 Update_Type = CASE 
      WHEN D1 LIKE '[0-9][/][0-9][0-9][/][0-9][0-9][0-9][0-9]%' THEN 1      -- 9/19/2009
      WHEN D1 LIKE '[0-9][/][0-9][/][0-9][0-9][0-9][0-9]%' THEN 2           -- 9/9/2009
      WHEN D1 LIKE '[0-9][0-9][/][0-9][0-9][/][0-9][0-9][0-9][0-9]%' THEN 3 -- 09/19/2009
      WHEN D1 LIKE '[0-9][0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9]%' THEN 4 -- 2009-01-09
               ELSE D2 END
FROM    #T  WHERE D1 <> ''

SELECT * FROM #T 

Yukarıdaki UPDATE ifadesini çalıştırdıktan sonra güncellenen verilerimizi gözlemleyelim :

--D1                    D2          Update_Type
--5/15/2006 18:11       20060515    1
--5/13/2006 17:24       20060513    1
--2007-02-07 11:38:07   20070207    4
--9/4/2008 00:38:45     20080904    2
--5/11/2006 0:47:52     20060511    1
--2007-01-04 12:43:00   20070104    4
--                      NULL        NULL
--  6/11/2006 0:47:52   NULL        NULL
--9/22/2002 07:20:07    20020922    1
--09/22/2002 07:20:07   20020922    3
--4/13/2007             20070413    1
--2/1/2007 20:23        20070201    2
--04-21-2011 18:47:42   NULL        NULL
--04-21-2008 17:11:00   NULL        NULL
--2/1/2007 11:44        20070201    2
--5/9/2007 9:22         20070509    2
--04/13/2007            20070413    3
--                      NULL        NULL
--2004-04-21 16:00:00   20040421    4
--04-21-2008 17:11:00   NULL        NULL

Bu tekniğin daha çok kayıtla toplu olarak uygulamasını ayrı bir yazıda görebilirsiniz :

Processing hundreds of millions records got much easier

Bu teknik ile log dosyanızın boyutu yaptığınız güncelleştirme işlemlerinde çok büyümeyecek ve size problem yaratmayacaktır.

Eğer her defasında tek formata uygun temizleme işlemi gerçekleştirmek istiyorsanız (örneğin 9/19/2009 formatı), aşağıdaki gibi bir script çalıştırmanız yeterli olacaktır.

UPDATE #T    
      SET D2 = SUBSTRING(D1,6,4) + '0' + REPLACE(LEFT(D1,5),'/',''),
      Update_Type = 1
WHERE D1 <> '' AND D1 LIKE '[0-9][/][0-9][0-9][/][0-9][0-9][0-9][0-9]%'

Aşağıdaki örneklerde görüleceği üzere LIKE operatörü yerine PATINDEX() fonksiyonu kullanılabilir. Örneğimizdeki gibi bir durumda LIKE operatörü ve PATINDEX() fonksiyonu performans açısından çok farklılık göstermemektedir. 300 milyon satır gibi çok kaydın olduğu tablolarda LIKE operatörü genelde hızlı çalışmaktadır fakat böyle büyük tablolarda az miktardaki (örneğin 14030 satır) kayıtlara herhangi bir patern uygulandığında LIKE operatörü PATINDEX() fonksiyonuna göre oldukça yavaş çalışmaktadır.

Bahsettiğim gibi büyük tabloda LIKE operatörü ile PATINDEX() fonksiyonunu aşağıdaki patern’ler ile test ettiğimde aradaki farkın ne kadar büyük olduğunu gözlemledim. LIKE operatörü ile sorgu 1 dakika 9 sn sürerken PATINDEX() fonksiyonu ile 10 sn sürmektedir. İşlem yaparken çalıştığınız kolon üzerinde index tanımlı olduğuna performans açısından dikkat ediniz.

SELECT  -- 10 saniye
    COUNT(D1)
FROM #T  
WHERE PATINDEX('[0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]%',D1)=1 AND D1 <>''

Aynı tabloda aşağıdaki sorgu 1 dakika 9 saniye sürmektedir.

SELECT    
   COUNT(D1)
FROM #T 
WHERE D1 <> '' AND D1 LIKE '[0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]%'

Farklı tipteki verilerin dönüşüm işlemlerinde PATINDEX() fonksiyonunun bazı durumlarda LIKE operatoründen daha hızlı olduğunu gözlemledik. Ancak farklı tiplerin tamamıda her zaman istediğimiz formata dönüştürülemeyeceğinide unutmamız gerekir, bu durumda bu verilerin artık olduğunu gözlemleyip gözardı edebiliriz. İşlem yaptığınız kolonun manipüle edilmiş halini içerecek bir kolonuda canlı (production) veri tabanının etkilenmemesi için bu işlemlerden önce tabloya eklememiz gerektiğini unutmamalıyız.

Sonuç olarak birbirinden farklı patern’lere sahip bir çok tarih formatı bulunmaktadır, fakat bu formatların bazırları CONVERT() fonksiyonu ile yapılan çevrimlerde hatalı sonuçlara sebep olmaktadır. Veri çevrimlerinde hataları en aza indirmek için en iyi çözüm string manüpülasyonları kullanmaktır.

Kaynak ;

String Manipulation on large data using LIKE operator or PATINDEX

Doron Farber bu makalesinin dışındada güzel makaleleri bulunmaktadır, okumanızı tavsiye ederim.

Processing hundreds of millions records got much easier

Data compression in large tables in MS SQL server

Remove duplicate records in sql

0 thoughts on “Veriler üzerinde LIKE ve PATINDEX operatörlerini kullanarak string manipülasyonları”

Bir cevap yazın