miércoles, 13 de marzo de 2013

SPLIT en Transact-SQL

Acá les dejo la forma en que podemos usar SPLIT en Transact-SQL.  Recordemos que en este lenguaje no existen los arreglos por lo que retornaremos el resultado en una tabla.

Solamente copien y peguen esta función;


CREATE FUNCTION [dbo].[SPLIT]( @STRING VARCHAR (8000), @DELIMITER NVARCHAR (10) ) RETURNS @VALUETABLE TABLE ([VALUE] VARCHAR(8000)) BEGIN
DECLARE @NEXTSTRING VARCHAR(8000)
DECLARE @POS INT  DECLARE @NEXTPOS INT
DECLARE @COMMACHECK NVARCHAR(1)  
 
SET @NEXTSTRING = ''
SET @COMMACHECK = RIGHT(@STRING,1)    
SET @STRING = @STRING + @DELIMITER  
SET @POS = CHARINDEX(@DELIMITER,@STRING)
SET @NEXTPOS = 1  
 
WHILE (@POS <>  0)  
BEGIN  
SET @NEXTSTRING = SUBSTRING(@STRING,1,@POS - 1)    

INSERT INTO @VALUETABLE ( [VALUE]) VALUES (@NEXTSTRING)    

SET @STRING = SUBSTRING(@STRING,@POS +1,LEN(@STRING))    
SET @NEXTPOS = @POS  
SET @POS  = CHARINDEX(@DELIMITER,@STRING)
END  

RETURN
END

Luego para usarla podemos hacerla de la siguiente manera:

DECLARE @Cliente AS VARCHAR(20)

SET @Cliente = 'CO,SI,SE'

SELECT * FROM dbo.SPLIT(@Cliente, ',')

Y obtendremos el siguiente resultado:

VALUE
CO
SI
SE

Espero esto puedas serles de utilidad.

2 comentarios:

  1. Pretty nice post. I just stumbled upon your blog and wanted to say that I've truly enjoyed surfing around your blog posts. After all I'll be subscribing to your rss feed and I hope you write again soon! yahoo mail login

    ResponderEliminar
  2. High ratio mortgage insurance premium calculation depending on rates which can be published by CMHC from time for you to time, but are not guaranteed to be correct in any way times and should be verified. mortgage payment calculator canada I included every repayment option available, and after that tried out each one to view the effects. mortgage calculator

    ResponderEliminar