Thursday 21 February 2013

Remove Delimeter And Get Strings In Separate Columns...



CREATE FUNCTION FN_SeparateDelimeterFromString (@string NVARCHAR(MAX),@separator NCHAR(1))
RETURNS @SeparatedStrings TABLE (string NVARCHAR(MAX))
AS
BEGIN
   DECLARE @position int
   SET @position = 1
   SET @string = @string + @separator
   WHILE charindex(@separator,@string,@position) <> 0
      BEGIN
         INSERT into @SeparatedStrings
         SELECT substring(@string, @position, charindex(@separator,@string,@position) - @position)
         SET @position = charindex(@separator,@string,@position) + 1
      END
     RETURN
END
 
Declare @Strings varchar(max)
Set @Strings='1,2,3,4,5,6,7'
select string from FN_SeparateDelimeterFromString(@Strings,',')

select string from FN_SeparateDelimeterFromString('N,I,T,I,S,H,,K,U,M,A,R',',')

Wednesday 20 February 2013

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   :)