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

2 comments: