locked
split the data on condition split RRS feed

  • Question

  •  hi  

     i have  a data it  has just like the   full name ( ' vera rama kanth'),( kanara ramanasai prasad) this  my full name 

     i want split  the data based  condition split:

    firstname, middlename, lastname

    vera,rama,kanth

    kanara,ramansai,prasard.

    how  to wirte the condition 

     thanks for advanced

    Thursday, May 14, 2015 1:22 PM

Answers

  • Please try this.

    How to Spilt a Field into Comma Delimited Elements:
    CREATE FUNCTION dbo.fnSplit(
        @sInputList VARCHAR(8000) -- List of delimited items
      , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
    ) RETURNS @List TABLE (item VARCHAR(8000))
    
    BEGIN
    DECLARE @sItem VARCHAR(8000)
    WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
     BEGIN
     SELECT
      @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
      @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
    
     IF LEN(@sItem) > 0
      INSERT INTO @List SELECT @sItem
     END
    
    IF LEN(@sInputList) > 0
     INSERT INTO @List SELECT @sInputList -- Put the last item in
    RETURN
    END
    GO
    
    select T.*, F.Item
    from CommaTable T
    CROSS APPLY dbo.fnSplit(T.myText, ',') F
    

    Also . . .

    DECLARE @tbl TABLE
    (
    C1 VARCHAR(50)
    )
    INSERT INTO @tbl
    SELECT 'A,B,C'
    SELECT C1
    	  ,C2 =SUBSTRING(C1,1,CHARINDEX(',',C1)-1)
    	  ,C3 =SUBSTRING(C1, CHARINDEX(',',C1)+1,CHARINDEX(',',C1,CHARINDEX(',',C1)+1) - (CHARINDEX(',',C1)+1))
    	  ,C4 =SUBSTRING(C1, CHARINDEX(',',C1,CHARINDEX(',',C1)+1)+1,LEN(C1))
      FROM @tbl
    


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, May 14, 2015 6:49 PM