Bir Tabloda Yer Alan Tüm Kolonlardaki Değerleri REPLACE Etmek

Bir tablodaki bir kolonda yer alan değerin içindeki bir karakteri başka bir karakter ile değiştirmek için REPLACE T-SQL komutunu kullanabiliriz.

Replace sql metin fonksiyonunun Books Online'daki tanımı aşağıdaki gibidir.


REPLACE ( 'string_expression1' , 'string_expression2', 'string_expression3' )

Code

Örnek olarak aşağıdaki sql cümleciklerini Northwind veritabanı üzerinde çalıştırabiliriz.


SELECT CompanyName FROM Customers WHERE CustomerID = 'BOLID'

Code

Sql ifadesi ile elde edeceğimiz sonuç "Bólido Comidas preparadas" olacaktır.

Aşağıdaki UPDATE sql ifadesi ile CompanyName kolonundaki değeri güncelliyoruz.
Kolon değerini güncellerken 'ó' karakterini 'o' ile değiştiriyoruz yani REPLACE ediyoruz.


UPDATE Customers

SET CompanyName = Replace(CompanyName, 'ó', 'o')

WHERE CustomerID = 'BOLID'

Code

Bu ifadeyi çalıştırdıktan sonra CompanyName kolonundaki değer artık "Bolido Comidas preparadas" olacaktır.

REPLACE ifadesini bir kolon için değilde tüm tablo için kullanmayı istersek bunu yapmanın bir yolu o tablo için tek tek tüm kolonlar için Replace ifadesi yazmak olacaktır.

Örneğin,


UPDATE TableReplace

SET

  Kolon1 = REPLACE(Kolon1, 'x', 'y'),

  Kolon2 = REPLACE(Kolon2, 'x', 'y'),

  .......,

  KolonN = REPLACE(KolonN, 'x', 'y')

Code

Peki, bu REPLACE ifadesini nasıl daha kullanışlı bir hale getirebiliriz?
Örneğin istediğimiz her tablo için bu ifadeyi kullanabilsek, ayrıca tek tek kolon isimleri ile de uğraşmak zorunda kalmasak ve kolonlardaki güncellemelerde kullanılacak eski ve yeni metin değerlerini parametrik olarak tanımlayabilsek.

Bunun için en uygun yöntem bir stored procedure yani "saklı yordam" yazmak olacaktır. Stored Procedure ifademizde üç parametre tanımlayacağız. Bunlardan birisi tablo ismini, diğer ikisi ise REPLACE ifadelerimize konu olacak olan eski ve yeni metin (string) değerlerdir.

Bu iş için oluşturulmuş ReplaceForAllColumns ismindeki stored procedure'ın tam kodunu aşağıda bulabilirsiniz.

Aşağıdaki CREATE TABLE ifadesi ile Stored Procedure içinde temporary bir tablo oluşturmaktayız. Bu oluşturduğunuz temporary tablo içinde parametre olarak geçeceğimiz tablodaki REPLACE için uygun statüde olan kolon isimlerini saklamakta kullanacağız.


CREATE TABLE #t1 (

   colName nvarchar(255)

)

Code

Burada uygun statüde derken aslında değeri sistem tarafından otomatik oluşturulan IDENTITY kolonları gibi olanları elemek istedim. Çünkü bu tip kolonlarda bir güncelleme yapmayacağız. REPLACE için uygun olabilecek kolonları belirleyeceğimiz ve temp tablomuza kaydedeceğimiz sql kodu aşağıdaki kod parçasıdır.


INSERT INTO #t1

SELECT Name FROM sysColumns WHERE id IN (

  SELECT id FROM SysObjects WHERE XType = 'U' AND Name = @TableName

) AND autoval IS NULL

Code

SELECT id FROM SysObjects WHERE XType = 'U' AND Name = @TableName ifadesini ile adını parametre olarak stored procedure 'a geçtiğimiz tablonun SysObjects sistem tablosundaki id değerini almakta kullanıyoruz. Ve bu id değeri ile ifade edilen tablodaki kolonları listelemek için de


SELECT Name FROM sysColumns WHERE id IN (

  SELECT id FROM SysObjects WHERE XType = 'U' AND Name = @TableName

)

Code

ifadesinden yararlanıyoruz.

autoval IS NULL koşulu ise bir SEED kullanılarak sistem tarafından oluşturularak değerleri verilmiş kolonları elemek için kullanılmış bir koşuldur.


SELECT

  @setlist = COALESCE(@setlist, '') + colname +

    ' = REPLACE(' + colName + ', ''' + @charOld + ''', ''' + @charNew + ''') ' + ','

FROM #t1

SELECT @setlist = substring(@setlist, 1, len(@setlist)-1)

SELECT @sql = 'UPDATE ' + @TableName + ' SET ' + @setlist

Code

Yukarıdaki sql cümleciklerinde COALESCE sql fonksiyonu kullanılarak dinamik bir sql ifadesi oluşturulmuş ve bu ifade @sql değişkenine atanmıştır. Bu oluşturulan sql ifadesini Print @sql komut satırı ile ekranda görüntüleyebiliriz.


UPDATE TestTableForReplace

SET

  title = REPLACE(title, ';', ':'),

  insertdate = REPLACE(insertdate, ';', ':'),

  insertedby = REPLACE(insertedby, ';', ':'),

  url = REPLACE(url, ';', ':'),

  comment = REPLACE(comment, ';', ':')

Code

Print @sql ile ekrana yazdırdığımız yukarıdaki sql ifadesinin konu ile ilgili olarak anlatılan ilk metod için verilen örneğe ne kadar benzediğine dikkat edelim.

Hazırladığımız stored procedure 'un sonunda dinamik olarak oluşturduğumuz sql ifadesini çalıştırmak için kullanacağımız EXEC sp_executesql komut satırı bulunmaktadır.


-- ========================================================================

-- ReplaceForAllColumns : Kodyaz.com

-- Bu stored procedure kullanılarak bir tablo içinde yeralan tüm kolonlarda

-- REPLACE iafedesinin ayrı ayrı çalışması sağlanarak, tablo bazında bir 

-- güncelleme yapılmaktadır.

-- ========================================================================

-- Kullanıma örnek :

-- EXECUTE ReplaceForAllColumns 'TestTableForReplace', ';', ':'



IF EXISTS (SELECT name FROM sysobjects WHERE name = N'ReplaceForAllColumns' AND type = 'P')

   DROP PROCEDURE ReplaceForAllColumns

GO



CREATE PROCEDURE ReplaceForAllColumns 

(

  @TableName as nvarchar(100),

  @charOld as nvarchar(100),

  @charNew as nvarchar(100)

)



AS



CREATE TABLE #t1 (

  colName nvarchar(255)

)



INSERT INTO #t1

SELECT Name FROM sysColumns WHERE id IN (

  SELECT id FROM SysObjects WHERE XType = 'U' AND Name = @TableName

) AND autoval IS NULL



DECLARE @sql AS nvarchar(4000)

DECLARE @setlist AS nvarchar(4000)



SELECT

  @setlist = COALESCE(@setlist, '') + colname +

    ' = REPLACE(' + colName + ', ''' + @charOld + ''', ''' + @charNew + ''') ' + ','

FROM #t1



SELECT @setlist = substring(@setlist, 1, len(@setlist)-1)

SELECT @sql = 'UPDATE ' + @TableName + ' SET ' + @setlist



-- Print @sql

DROP TABLE #t1



EXEC sp_executesql @sql



GO

Code

Hazırladığımız Stored Procedure 'ı test etmek için aşağıdaki script'i kullanarak bir test ortamı oluşturmaya çalışalım. Önce bir tablo oluşturalım, ardından da tabloyu örneğimiz için uygun veriler ile dolduralım.


CREATE TABLE [dbo].[TestTableForReplace] (

  [id] [int] IDENTITY (1, 1) NOT NULL ,

  [title] [nvarchar] (255) NULL ,

  [insertdate] [datetime] NULL ,

  [insertedby] [nvarchar] (100) NULL ,

  [url] [varchar] (255) NULL ,

  [comment] [nvarchar] (510) NULL 

) ON [PRIMARY]

GO



INSERT INTO TestTableForReplace

SELECT

  N',r er;er t,ErT,Ert e;r Ter;er; gE,r',

  GetDate(),

  N'4erte,erger ergt e; er;er gE, erge;g e;, er',

  ', ege;E rett;e etHet,er er t, w',

  N' E;er ;e yrT, r, Ery e;eR ;ER, e;'

GO

INSERT INTO TestTableForReplace

SELECT

  N'şl,3453l5;3456345,34534;345,345',

  NULL,

  'we e,w t;er e,E rT,Er ; R,,',

  '; WR;;wrt ;4;T,e r',

  ', T,34;43; ,+,;'

GO

Code

"SELECT * FROM TestTableForReplace" ifadesi ile test verilerimizi görebiliriz.
Şimdi tüm tablodaki ";" ifadelerini ":" ile değiştirelim.
Bunun için kullanacağımız sql scripti aşağıdaki gibi olacaktır.


EXECUTE ReplaceForAllColumns 'TestTableForReplace', ';', ':'

Code

Not: COALESCE fonksiyonunun kullanımı ile ilgili olarak başka örnekler için COALESCE Kullanılarak UserDefinedFunction Yardımı ile Bir Kitabın Yazarlarının Okunması ve COALESCE fonksiyonu ile bir tablonun bir kolonundaki değerleri bir string haline getirmek başlıklı konuları inceleyebilirsiniz.