Play With Strings

1. Make Strings Shorter.

Like My Name   Nitish Kumar Jha-> N K Jha.



CREATE FUNCTION FN_MakeStringsShorter (@name varchar(max))
RETURNS varchar(max)
AS BEGIN
  DECLARE @cutpos int, @spacepos int, @result varchar(max);
  DECLARE @cutlist TABLE (CutPos int, SpacePos int);

  SET @result = LTRIM(RTRIM(@name));

  SET @cutpos = 2;
  SET @spacepos = CHARINDEX(' ', @result);
  WHILE @spacepos > 0 BEGIN
    INSERT INTO @cutlist VALUES (@cutpos, @spacepos);
    SET @spacepos = @spacepos + 1;
    SET @cutpos = @spacepos + 1;
    SET @spacepos = CHARINDEX(' ', @result, @spacepos);
  END;

  DELETE FROM @cutlist WHERE CutPos >= SpacePos;

  SELECT @result = STUFF(@result, CutPos, SpacePos - CutPos, '')
  FROM @cutlist
  ORDER BY CutPos DESC;

  RETURN @result;
END;

SELECT dbo.FN_MakeStringsShorter('Nitish Kumar Jha');
SELECT dbo.FN_MakeStringsShorter('Prakash Kumar Lele');





2. Separate Strings

CREATE FUNCTION FN_GetFirstLetter ( @Original_String VARCHAR(1000), @Separator VARCHAR(10))  
RETURNS @ModifiedValue TABLE  
    (Nitish VARCHAR(100))
AS
BEGIN
    DECLARE @separator_position INT  
    DECLARE @array_value VARCHAR(1000)  
 
    SET @Original_String = @Original_String + @separator
 
    WHILE patindex('%' + @separator + '%' , @Original_String) <> 0  
    BEGIN
 
      SELECT @separator_position =  patindex('%' + @separator + '%', @Original_String)
      SELECT @array_value = left(@Original_String, @separator_position - 1)
 
        INSERT @ModifiedValue
        VALUES (SUBSTRING(Cast(@array_value AS varchar), 1, 1))
 
      SELECT @Original_String = stuff(@Original_String, 1, @separator_position, '')
    END
 
    RETURN
END

select Nitish from FN_GetFirstLetter('Nitish Kumar Jha From Delhi',' ')
select Nitish from FN_GetFirstLetter('Nitish, Kumar, Jha, From, Delhi.',' ')

select Nitish from FN_GetFirstLetter('Nitish,Kumar,Jha,From,Delhi.',',')



ALL THE BEST!!1   :)