Home Forums SQL Server 2008 SQL Server 2008 - General Extract text which is between 1st and 2nd comma RE: Extract text which is between 1st and 2nd comma

  • October 22, 2012 at 5:44 am

    #1551314

    probably the best most recommended way would be using the delimited split function.

    there's also a CHARINDEX2 custom function you could use as well.

    /*

    --Results

    val Item

    SHOP NO 66/67, PATEL HERITAGE, PLOT NO 15/17, SECTOR 7, KHARGHAR, NAVI MUMBAI 410210 PATEL HERITAGE

    NAYASARAK, CUTTACK, DISTRICT CUTTACK, ORISSA-753002 CUTTACK

    */

    with MySampleData(val)

    AS

    (

    SELECT ' SHOP NO 66/67, PATEL HERITAGE, PLOT NO 15/17, SECTOR 7, KHARGHAR, NAVI MUMBAI 410210' UNION ALL

    SELECT 'NAYASARAK, CUTTACK, DISTRICT CUTTACK, ORISSA-753002'

    )

    SELECT MySampleData.*,myFn.Item

    FROM MySampleData

    CROSS APPLY dbo.DelimitedSplit8K(val,',') myFn

    WHERE myFn.ItemNumber=2

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!