>SQL Server: A Simple Method to Get Specific Part of String


SUBSTRING is a nice function to get a part of string, but when a string consists of many parts, getting specific part of it, is a bit painful task. Hopefully, following function will help you to achieve such tasks.

–| Create By: Aasim Abdullah
–| Description: Function takes a string and section number of string which
–|              is required and separator, which separates different
–|              sections of given string
–| How To Use: SELECT dbo.usp_stringpart(‘First,Second,Third,Forth,Fifth’,3,’,’)
CREATE FUNCTION dbo.usp_stringpart
            @InputString NVARCHAR(1000),
            @Section SMALLINT,
            @Separator NCHAR(1)
                  @StartPosition INT,
                  @EndPosition INT,
                  @Cycle INT,
                  @ResultString NVARCHAR(1000)
            SELECT @Cycle = 0,
                        @StartPosition = 0
                        WHILE @Cycle < @Section1
                                    SELECT @StartPosition = CHARINDEX(@Separator,@InputString,@StartPosition)+1
                                    IF @StartPosition = 1
                                          SELECT      @Cycle = @Section
                                          SELECT @Cycle = @Cycle + 1
            SELECT @EndPosition  = CHARINDEX(@Separator,@InputString,@StartPosition)
            SELECT @ResultString = LTRIM(RTRIM(SUBSTRING(@InputString,@StartPosition,
                                                            CASE @EndPosition WHEN 0
                                                                              THEN (LEN(@InputString)+1)-@StartPosition
                                                                                    ELSE (@EndPosition@StartPosition) END)))
      RETURN @ResultString
Hopefully, you will modify it to handle invalid sections i.e. in our example case if, only 4 section are in string but you provide if number 5 as section it will return you first part. Modify it, so it should return NULL for such cases. 😉

Posted on February 2, 2011, in TSQL Tips n Tricks. Bookmark the permalink. 1 Comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: