none
Substring Help in the query RRS feed

  • Question

  • Experts,

    I've some sample strings as :-

    3805 Western~~Mattoon~IL~61938
    3805 Western~Apt205~Mattoon~IL~61938

    I want to split into 5 different.

    My expected o/p as:-

    SELECT	'3805 Western' AS 'AddressLine1', NULL AS 'AddressLine2', 'Mattoon' AS 'City', 'IL' AS 'State', '61938' AS 'PostalCode'
    UNION ALL
    SELECT	'3805 Western' AS 'AddressLine1', 'Apt205' AS 'AddressLine2', 'Mattoon' AS 'City', 'IL' AS 'State', '61938' AS 'PostalCode'

    Thank You

    Apex


    Please do let us know your feedback. Thank You - KG, MCTS

    Thursday, August 2, 2012 2:19 PM

Answers

  • Hi,

    Hope this line of code will help you.

    Declare @tbl table(Addr varchar(max))

    insert into @tbl values('3805 Western~~Mattoon~IL~61938'),('3805 Western~Apt205~Mattoon~IL~61938')

    Declare @tblXML table(Addr XML)

    insert into @tblXML

    Select '<Address><row>'+REPLACE(Addr,'~','</row><row>')+'</row></Address>' from @tbl

    select

          adrs.col.value('row[1]','varchar(max)') as AddressLine1

          ,adrs.col.value('row[2]','varchar(max)') as AddressLine2

          ,adrs.col.value('row[3]','varchar(max)') as city

          ,adrs.col.value('row[4]','varchar(max)') as [state]

          ,adrs.col.value('row[5]','varchar(max)') as PostalCode

    from

    @tblXML

    cross apply

    Addr.nodes('Address') as adrs(col)


    Aftab Ansari

    • Marked as answer by gk1393 Thursday, August 2, 2012 4:55 PM
    Thursday, August 2, 2012 2:38 PM
  • Declare @var_SplitChar as varchar(1000),@var_inputString varchar(1000)
    set @var_inputString='3805 Western~ ~Mattoon~IL~61938'
    set @var_SplitChar='~'
    ;WITH Split AS 
    		(       
    			SELECT 0 as FirstCharIndex ,CHARINDEX(@var_SplitChar, @var_inputString) as NextCharIndex        
    			UNION ALL      
    			SELECT NextCharIndex,CHARINDEX(@var_SplitChar, @var_inputString, NextCharIndex + 1)       
    			FROM Split       
    			WHERE NextCharIndex > 0     
    		)     
    SELECT SUBSTRING(@var_inputString, FirstCharIndex + 1,case when NextCharIndex = 0 then len(@var_inputString)                  
    										       else NextCharIndex - FirstCharIndex -1 end) as Result,    
    	   FirstCharIndex,	
    	   NextCharIndex,
    	   @var_inputString
    FROM Split
    Try this
    • Marked as answer by gk1393 Thursday, August 2, 2012 4:55 PM
    Thursday, August 2, 2012 2:55 PM

All replies

  • Please refer the below link:

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/a394b252-82dc-451d-986a-ab727ed15020


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Thursday, August 2, 2012 2:23 PM
  • Hi,

    Hope this line of code will help you.

    Declare @tbl table(Addr varchar(max))

    insert into @tbl values('3805 Western~~Mattoon~IL~61938'),('3805 Western~Apt205~Mattoon~IL~61938')

    Declare @tblXML table(Addr XML)

    insert into @tblXML

    Select '<Address><row>'+REPLACE(Addr,'~','</row><row>')+'</row></Address>' from @tbl

    select

          adrs.col.value('row[1]','varchar(max)') as AddressLine1

          ,adrs.col.value('row[2]','varchar(max)') as AddressLine2

          ,adrs.col.value('row[3]','varchar(max)') as city

          ,adrs.col.value('row[4]','varchar(max)') as [state]

          ,adrs.col.value('row[5]','varchar(max)') as PostalCode

    from

    @tblXML

    cross apply

    Addr.nodes('Address') as adrs(col)


    Aftab Ansari

    • Marked as answer by gk1393 Thursday, August 2, 2012 4:55 PM
    Thursday, August 2, 2012 2:38 PM
  • Declare @var_SplitChar as varchar(1000),@var_inputString varchar(1000)
    set @var_inputString='3805 Western~ ~Mattoon~IL~61938'
    set @var_SplitChar='~'
    ;WITH Split AS 
    		(       
    			SELECT 0 as FirstCharIndex ,CHARINDEX(@var_SplitChar, @var_inputString) as NextCharIndex        
    			UNION ALL      
    			SELECT NextCharIndex,CHARINDEX(@var_SplitChar, @var_inputString, NextCharIndex + 1)       
    			FROM Split       
    			WHERE NextCharIndex > 0     
    		)     
    SELECT SUBSTRING(@var_inputString, FirstCharIndex + 1,case when NextCharIndex = 0 then len(@var_inputString)                  
    										       else NextCharIndex - FirstCharIndex -1 end) as Result,    
    	   FirstCharIndex,	
    	   NextCharIndex,
    	   @var_inputString
    FROM Split
    Try this
    • Marked as answer by gk1393 Thursday, August 2, 2012 4:55 PM
    Thursday, August 2, 2012 2:55 PM