17
Oct

GetPart – A really fast SQL Server version of Instr

One of the functions most useful in programming is Instr. This routine gets parts of strings – yet the funtion is missing in SQL Server. Well, its missing no more – welcome to my version which I call GETPART.

This function returns part of a string based on a seperator character

As an example
select GetPart(“the~cat~sat~on”,’~',2)
returns “cat”

So imagine you have a persons name in a column of “Jaffa Brown” – Getpart(Name,’ ‘,1) will get you the first name. What about a postcode of “RG22 4TT” – Getpart(PostCode,’ ‘,1) will get the first segment.

Of course – its much more useful than that – you have a column in a table that is CSV seperated and you want the 5th part to the string, just use Getpart(columnname,’,',5)

The function has 3 parameters:
1) The string (which can be a column name)
2) The seperator character (in single quotes)
3) the item number you want returned

So as an example, if you have a column PCT which has the format “5C5-North East Trust” and you just want the PCT code (not the name), just use…

SELECT GetPart(PCT,’-',1) from yourTable

Job done!

the function which you need to run in your database to create the GetPart function is as follows:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetPart]
(
      @RowData nvarchar(4000),
      @SplitOn nvarchar(5),
      @GetPart int
)
RETURNS varchar(4000)
AS
BEGIN
      Declare @Cnt int
      Set @Cnt = 1
          declare @RetValue nvarchar(4000)
       set @RowData = @RowData + @SplitOn
        -- Skip those parts not required
      While (Charindex(@SplitOn,@RowData)>0 and @Cnt <= @GetPart)
      Begin
                        if (@Cnt = @GetPart) set @RetValue = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
            Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
            Set @Cnt = @Cnt + 1
      End
          Return(@RetValue)
END