Thursday, December 9, 2010

MS-SQL - Get the first character in each word

The below function will get the string as input and will give each and every character in starting of the words.

Step 1
Create a user defined GetFirstCharacter function,it is look like this
 

CREATE FUNCTION dbo.GetFirstCharacter(@StringValue nvarchar(max))
RETURNS nvarchar(max)
AS
    BEGIN
    
        DECLARE @FirstChar nvarchar(10) -- get the first character
        DECLARE @StringValueLength int -- length of given string
        DECLARE @Index int  -- Set Start Index of while loop
        DECLARE @Flag bit  -- Check for Blank Space
    
    
        SET @StringValueLength=LEN(@StringValue) -- get lenght of given string
        SET @Index=1 
        SET @Flag=0  
    
        IF ((@StringValue IS NOT NULL) AND (@StringValue<>''))
            BEGIN
                    -- get the first charcter of the first word
                    SET @FirstChar=LEFT(@StringValue,1) 
                    
                    -- get each character by using while loop
                    WHILE (@Index<=@StringValueLength)
                        BEGIN
                            
                            -- Check the blank space 
                            IF(SUBSTRING(@StringValue,@Index,1) = ' ')
                                BEGIN
                                    SET @Flag=1
                                END
                            ELSE
                                BEGIN 
                                    SET @Flag=0
                                END
                            
                            
                            IF(@Flag=1)
                                BEGIN
                                             -- increment the index after the blank space
                                            SET @Index=@Index+1
                                            
                                            -- Get the each character from each word
                                            SET @FirstChar = @FirstChar + SUBSTRING(@StringValue,@Index,1)
                                        
                                        
                                END
                            
                            SET @Index=@Index+1  -- While Counter
                        END
            END 
    
        RETURN @FirstChar -- return the value
    
    END

Step 2
Call a user defined GetFirstCharacter function,it is look like this

SELECT dbo.GetFirstCharacter('Yogesh Naik') AS 'First character of the each word'


Download
Download Sql Script

3 comments:

  1. Replies
    1. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a .Net developer learn from Dot Net Training in Chennai. or learn thru Dot Net Training in Chennai. Nowadays Dot Net has tons of job opportunities on various vertical industry.
      or Javascript Training in Chennai. Nowadays JavaScript has tons of job opportunities on various vertical industry.

      Delete