This is a very useful script written for a purpose but I think it can be used and adapted to many different purposes. It basically gets a substring of a string, but you can specify which substring you want. For example, you want to get the first part of a string with a delimiter of ":"
SELECT dbo.fn_GetPart('JOHN:JOE:MARIO', 1, ':', 0)
Result: JOHN
Or you want to get the third part of a string with a delimiter of ":"
SELECT dbo.fn_GetPart('JOHN:JOE:MARIO', 3, ':', 0)
Result: MARIO
Or if you want to get the third part but it contains more ":" in the text and you want it all:
SELECT dbo.fn_GetPart('JOHN:JOE:MARIO SMITH: MANAGER', 3, ':', 1)
Result: MARIO SMITH: MANAGER
I just wrote it, finished today, so if there are any bugs, let me know. Hope this is usefull!
/*
*** Object: Function [dbo].[fn_GetPart]
*** Data: 10/05/2009
*** Description: Returns the nth part of a string
*** Must specify a delimiter
*** Autor: Aecio Lemos (VLS Web)
*** Parameters:
*** @INSTRING - The original string
*** @PART - Which part you want (numeric, 1 = first part, etc.)
*** @DELIM - The field delimiter (such as a comma or pipe)
*** @END - If this is the last part. This is useful in case you have
*** spaces in the latter part of your original string
*** Comments: This script was originally created to split DNS Records into fields
*** example:
*** SELECT dbo.fn_GetPart('TEXT 120 TXT v=spf1 mx a:beta.vlsweb.com.br -all', 4, ' ', 1)
*** the @END parameter was added so I could get the last part of the record
*** that contains spaces
*/
CREATE Function dbo.fn_GetPart(
@INSTRING AS VARCHAR(1000),
@PART AS INT,
@DELIM AS VARCHAR(10),
@END AS BIT = 0)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @I INT
DECLARE @D INT
DECLARE @START INT
DECLARE @LEN INT
DECLARE @CHAR CHAR(1)
DECLARE @RET VARCHAR(100)
SET @I = 1
SET @D = 0
SET @LEN = LEN(@INSTRING)
SET @START = 1
WHILE @I <= @LEN
BEGIN
SET @CHAR = SUBSTRING(@INSTRING, @I, 1)
IF @CHAR = @DELIM
BEGIN
--DELIMITADOR ENCONTRADO
SET @D = @D + 1
--ENCONTRADO UM DELIMITADOR
--VERIFICAR SE ESTA É A PARTE QUE QUEREMOS
IF @D = @PART
BEGIN
--SIM, RETORNAR A PARTE
IF @END = 0
SET @RET = SUBSTRING(@INSTRING, @START, @I - @START)
ELSE
SET @RET = SUBSTRING(@INSTRING, @START, @LEN - @START)
BREAK
END
ELSE
BEGIN
SET @START = @I + 1
END
END
SET @I = @I + 1
END
--SE CHEGOU AO FINAL E NÃO ENCONTROU DELIMITADOR, MOSTRA O ÚLTIMO SEGMENTO
IF (@D > 0) AND (@D = @PART - 1)
BEGIN
SET @RET = SUBSTRING(@INSTRING, @START, LEN(@INSTRING) - @START + 1)
END
RETURN @RET
END
Aécio Lemos
http://www.vlsweb.com.br
O primeiro provedor de hospedagem gerenciada do Brasil