Function to return part of a delimited text

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Function to return part of a delimited text

Author

Topic

alemos
Starting Member

16 Posts

Posted - 2009-05-13 : 00:22:43

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources