Nove funkcije za konvertovanje podataka u MS SQL Serveru 2012

Za konvertovanja tipova podataka na MS SQL Server-u se obično koriste dve funkcije CAST i CONVERT ali se je pokazalo da korišćenje tih funkcija nije baš uvek udobno. Zato MS SQL Server 2012 donosi neke novine.

Sintaksa za CAST i CONVERT je:

  • CAST ( expression AS data_type [ ( length ) ] )
  • CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

gde su:

  • expression – bilo koji validni izraz,
  • data_type – ciljni tip podataka,
  • length – opcioni Integer koji određuje dužinu ciljnog tipa podataka (default vrednost je 30),
  • style – integer vrednost kojom se određuje kako će biti formatirana izlazna vrednost iz CONVERT funkcije.

Svi detalji se mogu naći na MSDN-u.

Primer 1.

select cast(11.125 as int) as Rezultat

Izvršavanjem ovog izraza dobija se:

Rezultat ———– 11

(1 row(s) affected)

Primer 2.

declare @Datum datetime select @Datum = convert(datetime, '2012-02-12')
print 'style 104: ' + convert(varchar(20), @Datum, 104)
print 'style 102: ' + convert(varchar(20), @Datum, 102)

Izvršavanjem ovog izraza dobija se:

Messages

style 104: 12.02.2012

style 102: 2012.02.12

Naravno, ne mogu se svi tipovi konvertovati u sve tipove. Kad se pokuša da se neki podatak konvertuje u u nekonvertibilni tip doći će do greške. Na primer, ne možete niz karaktera ‘crveno’ koji može da bude tipa varchar(50) konvertovati u datum tipa date ili datetime. Ovakav pokušaj će rezultovati greškom. Problem je što se u praksi veoma često javlja potreba da se podaci iz nekog tekstualnog fajla ili Excel fajla prebace u tabelu. Obično se takvi podaci inicijalno prebacuju u tabelu gde su sve kolone varchar a tek u sledećem koraku se prebacuju u ciljne tabele. Problem je što kod prebacivanja u ciljne tabele podaci moraju da budu konvertovani u ciljne tipove. Kako selektovati podatke i istovremeno ih konvertovati u željene tipove a da se ne generiše greška? Vrlo često bi odgovarajuće rešenje bilo da u onim redovima gde se konkretni podatak može konvertovati dobijemo konvertovan podatak a da u suprotnom dobijemo null? Mehanizam prebacivanja zapis po zapis nije rešenje (iako se često njemu pribegava) pošto se može doći u poziciju da neki SSIS paket ili prekine rad zbog greške i uradi rollback ili ne prebaci sve podatke zato što ne može da uradi konverziju.

Od verzije MS SQL Server-a 2005 moguće je koristiti TRY CATCH da bi se hendlovalo izvršavanje nekog TSQL izraza:

Primer 3.

---------bez ulaska u catch------------------
declare @datum date
begin try
select @datum = convert(datetime, '1.1.2012')
end try
begin catch
select @datum = null
end catch;

select @datum as datum

Izvršavanjem ovog izraza dobija se:

datum ———- 2012-01-01

(1 row(s) affected)

Primer 4.

---------sa ulaskom u catch----------------
declare @datum date

begin try
select @datum = convert(datetime, 'goran')
end try
begin catch
select @datum = null
end catch;

select @datum as datum

Izvršavanjem ovog izraza dobija se:

datum ———- NULL

(1 row(s) affected)

Problem je što se TRY CATCH struktura ne može upotrebljavati kao deo SELECT klauzule. U tom slučaju je moguće koristiti sledeći TSQL izraz za konverziju neke varchar kolone u kolonu tipa int:

Primer 5.

declare @BrojProdatih as varchar(100) = '567'

select BrojProdatih = case when isnumeric(@BrojProdatih) = 1
and @BrojProdatih not like '%[^0-9?-+ ]%' escape '?'
and cast(@BrojProdatih as numeric(38, 0))
between -2147483648 and 2147483647
then cast(@BrojProdatih as int)
end;

Izvršavanjem ovog izraza dobija se:

BrojProdatih ———— 567

(1 row(s) affected) Ako se podatak ne može konvertovati onda će izlaz biti NULL:

Primer 6.

declare @BrojProdatih as varchar(100) = '567g'

select BrojProdatih = case when isnumeric(@BrojProdatih) = 1
and @BrojProdatih not like '%[^0-9?-+ ]%' escape '?'
and cast(@BrojProdatih as numeric(38, 0)) between -2147483648 and 2147483647
then cast(@BrojProdatih as int)
end;

Izvršavanjem ovog izraza dobija se:

BrojProdatih ———— NULL

(1 row(s) affected)

Za konvertovanje podataka iz neke varchar kolone u neki datumski tip bi mogao biti korišćen izraz:

Primer 7.

declare @DatumProdaje varchar(20) = '1.2.2012'

select DatumProdaje = case when isdate(@DatumProdaje) = 1 then
convert(datetime, @DatumProdaje)
else null
end;

Izvršavanjem ovog izraza dobija se:

DatumProdaje ———————– 2012-01-02 00:00:00.000

(1 row(s) affected)

Ipak ponekad ni ovakvi izrazi nisu rešenje. Rešenje može biti kreiranje funkcije sa TRY CATCH blokom ali korišćenje funkcija u SELECT klauzuli izuzetno usporava izvršavanje (čitaj skupo je).

Rešenje donosi MS SQL Server 2012 uvođenjem funkcija TRY_CONVERT i TRY_PARSE.

Sintaksa za TRY_CONVERT je:

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

gde su:

  • data_type [ ( length ) ] – tip podataka u koji se radi konverzija,
  • expression – vrednost koja se konvertuje,
  • style – integer vrednost kojom se određuje kako će biti formatirana izlazna vrednost.

Ako ulazna vrednost u funkciju može da se konvertuje funkcija će ga konvertovati a ako nije onda će funkcija vratiti NULL.

Detalji se mogu naći na MSDN-u.

Primer 8.

--SQL Server 2012
declare @DatumProdaje datetime = '1.2.2012'
select try_convert(datetime, @DatumProdaje) as DatumProdaje

Izvršavanjem ovog izraza dobija se:

DatumProdaje ———————– 2012-01-02 00:00:00.000

(1 row(s) affected)

Primer 9.

select case when try_convert(float,'test') is null
then 'Greška u konverziji'
else 'Uspešna konverzija'
end as Rezultat;

Izvršavanjem ovog izraza dobija se:

Rezultat ———— Greška u konverziji
(1 row(s) affected)

Sintaksa za TRY_PARSE je:

TRY_PARSE ( string_value AS data_type [ USING culture ] )

gde su:

  • string_value – nvarchar(4000) vrednost koja treba da se parsira u željeni tip podataka,
  • data_type – tip podataka u koji se vrši konverzija,
  • culture – opcioni string kojim se definiše kako će izlazna vrednost biti formatiran (ako ovaj argument nije validann funkcija će vratiti grešku.

Ako ulazna vrednost u funkciju može da se konvertuje funkcija će ga konvertovati a ako nije onda će funkcija vratiti NULL.

Detalji se mogu naći na MSDN-u.

Primer 10.

select try_parse('Goran' as datetime2 using 'en-US') as Rezultat

Izvršavanjem ovog izraza dobija se:

Rezultat ————— NULL

(1 row(s) affected)

Primer 11.

select
case when try_parse('Goran' AS decimal using 'sr-Latn-CS') is null
then 'True'
else ‘False'
end as Rezultat;

Izvršavanjem ovog izraza dobija se:

Rezultat ————— False

(1 row(s) affected)

Leave a Reply

%d bloggers like this: