HOW TO SPLIT THE COLUM BY SQL QUERY ? RSS

• • Reply
  

  agkaiser

  Member

  8 Points

  46 Posts

  HOW TO SPLIT THE COLUM BY SQL QUERY ?

  Nov 24, 2012 05:40 AM|LINK

  Hi friends ,

  I am having a Vehicle table .

  Eg

  COLUM1  COLUM2

  M0001    TATA||SRL00001

  M0002    TOYOTO||SRL00002

  M0003    MARUTHI||SRL00003

  I want to  SELECT COLUM1 AND SECOND PART OF COLUM2 (SERIAL NO)

   

  OUTPUT SHOULD BE LIKE BELOW

  COLUM1  COLUM2

  M0001    SRL00002

  M0002    SRL00002

  M0003    SRL00003

  HOW TO SPLIT THE COLUM2 BY SQL QUERY

   

  THANKS

    

  • Moved by mbanavige on Nov 24, 2012 03:34 PM
• • Reply
  

  me_ritz

  Star

  9337 Points

  1447 Posts

  Re: HOW TO SPLIT THE COLUM BY SQL QUERY ?

  Nov 24, 2012 06:27 AM|LINK

  Create this split function in the DB

  (Refer to - http://itdeveloperzone.blogspot.in/2012/07/split-function-in-sql.html)

  CREATE FUNCTION split(
      @delimited NVARCHAR(MAX),
      @delimiter NVARCHAR(100)
  ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
  AS
  BEGIN
      DECLARE @xml XML
      SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
  
      INSERT INTO @t(val)
      SELECT  r.value('.','varchar(MAX)') as item
      FROM  @xml.nodes('/t') as records(r)
      RETURN
  END

  then run this script (you need to chage the table name according to yours)

  DECLARE @TAB TABLE (COLUM1 VARCHAR(10),  COLUM2 VARCHAR(50))
  
  INSERT INTO @TAB
  SELECT 'M0001','TATA||SRL00001' UNION ALL
  SELECT 'M0002','TOYOTO||SRL00002' UNION ALL
  SELECT 'M0003','MARUTHI||SRL00003' 
  
  SELECT T.COLUM1, s.val 
  FROM @TAB T
  CROSS APPLY split(T.COLUM2, '||') S
  WHERE S.id = 2

  
  
  
  

  Please mark the post as "Answer" that helps you.
  • Marked as answer by Catherine Shan - MSFT on Dec 03, 2012 12:58 AM
• • Reply
  

  nikunjnandan...

  Participant

  882 Points

  223 Posts

  Re: HOW TO SPLIT THE COLUM BY SQL QUERY ?

  Nov 26, 2012 01:02 PM|LINK

  Hii

  Try this

  DECLARE @Temp AS VARCHAR(50)
   SET @Temp= 'TATA||SRL00001'
   
   DECLARE @Temp2 AS VARCHAR(50)
   SET @Temp2= 'TOYOTO||SRL00002'
   
   DECLARE @Temp3 AS VARCHAR(50)
   SET @Temp3= 'MARUTHI||SRL00003'
   
   
   SELECT RIGHT(@Temp,LEN(@temp) - CHARINDEX('||',@Temp,0)-1)
   SELECT RIGHT(@Temp2,LEN(@temp2) - CHARINDEX('||',@Temp2,0)-1)
   SELECT RIGHT(@Temp3,LEN(@temp3) - CHARINDEX('||',@Temp3,0)-1)
   
   --OR
   
   SELECT SUBSTRING(@Temp,CHARINDEX('||',@Temp,0) + 2,LEN(@Temp))
   SELECT SUBSTRING(@Temp2,CHARINDEX('||',@Temp2,0) + 2,LEN(@Temp2))
   SELECT SUBSTRING(@Temp3,CHARINDEX('||',@Temp3,0) + 2,LEN(@Temp3))

  
  While use this in your table just replace @temp variable with your table field name.

  Regards,
  Nikunj Nandaniya
  My Blog
  • Edited by nikunjnandaniya on Nov 26, 2012 01:04 PM