>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)
RETURNS NVARCHAR(1000)
BEGIN
      DECLARE
                  @StartPosition INT,
                  @EndPosition INT,
                  @Cycle INT,
                  @ResultString NVARCHAR(1000)
            SELECT @Cycle = 0,
                        @StartPosition = 0
                        WHILE @Cycle < @Section1
                              BEGIN
                                    SELECT @StartPosition = CHARINDEX(@Separator,@InputString,@StartPosition)+1
                                    IF @StartPosition = 1
                                          SELECT      @Cycle = @Section
                                    ELSE                                           
                                          SELECT @Cycle = @Cycle + 1
                                         
                 
                              END
            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
END
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. 😉
Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: