Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How can i find the value between these two delimiters

Answered How can i find the value between these two delimiters

  • Friday, February 08, 2013 7:56 PM
     
     
    Sign In to Vote
    0

    Hi i have a string for example '1126_Construction_037_Hardwood veneers'

    i need to find the value after first delimiter (_) and he next delimiter (_)

    basically the value Construction

     

All Replies

  • Friday, February 08, 2013 8:30 PM
     
     
    Sign In to Vote
    0

    Hi RAGS1109,

    you can use a function to split such kind of lines and after that you can get what you are required

     
  • Friday, February 08, 2013 8:32 PM
     
      Has Code
    Sign In to Vote
    1

    Here is a source code of this function

    CREATE FUNCTION dbo.[SplitText](@String nvarchar(max), @Delimiter nvarchar(10))       
returns @temptable TABLE (txt nvarchar(max)) 
	WITH SCHEMABINDING      
as       
begin       
    declare @idx int       
    declare @slice nvarchar(max)       
      
    select @idx = LEN(@Delimiter)       
        if len(@String)<LEN(@Delimiter) or @String is null  return       
      
    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx-1)       
        else       
            set @slice = @String       
          
        if(len(@slice)>0)  
            insert into @temptable(txt) values(@slice)       
  
		declare @shift int = len(@String) - @idx-LEN(@Delimiter) + 1
		if @shift<0
			SET @shift=0
        set @String = right(@String,@shift)         
        if len(@String) = 0 break       
    end   
return       
end  

GO

     
  • Friday, February 08, 2013 8:35 PM
     
     Answered Has Code
    Sign In to Vote
    0
     
    declare @test table(foo varchar(100));
insert @test(foo) Values ('1126_Construction_037_Hardwood veneers');
with cteStart as
(select foo, CharIndex('_', foo) As StartPos
from @test),
cteEnd As
(select foo, StartPos, CharIndex('_', Substring(foo, StartPos+1, Len(foo)))As EndPos
From cteStart)
Select SubString(foo, StartPos+1, Case When EndPos = 0 Then Len(foo) Else EndPos-1 End)
From cteEnd;

    Tom
    • Proposed As Answer by Naarasimha Friday, February 08, 2013 9:32 PM
    • Marked As Answer by Iric WenModerator Monday, February 18, 2013 7:07 AM
    •  
     
  • Friday, February 08, 2013 9:05 PM
    Moderator
     
     Proposed Answer Has Code
    Sign In to Vote
    0
     
    declare @s varchar(200)='1126_Construction_037_Hardwood veneers'
 

DECLARE @myXML AS XML  = N'<H><r>' + Replace(@s, '_', '</r><r>') + '</r></H>'
 
SELECT distinct Vals.id.value('(/H/r)[2]', 'NVARCHAR(50)')  AS val

FROM @myXML.nodes('/H/r') AS Vals(id)

    • Proposed As Answer by Peréz Friday, February 08, 2013 9:29 PM
    •  
     
  • Friday, February 08, 2013 9:24 PM
     
     Proposed Answer Has Code
    Sign In to Vote
    0

    Hi RAGS1109

    Wanted to share my two cents on this:

    DECLARE @MyText TABLE (mytext VARCHAR(MAX))
INSERT INTO @MyText (mytext) VALUES
('1126_Construction_037_Hardwood veneers'),
('1126_Painting_037_Hardwood veneers'),
('1126_Electrical_037_Hardwood veneers'),
('1126_Plumbing_037_Hardwood veneers')

SELECT Category = SUBSTRING(mytext,PATINDEX('%[0-9]_[a-z]%',mytext) + 2,(PATINDEX('%[a-z]_[0-9]%',mytext) - PATINDEX('%[0-9]_[a-z]%',mytext) - 1))
FROM @MyText

    Pérez

    • Proposed As Answer by Naarasimha Friday, February 08, 2013 9:33 PM
    •  
     
  • Friday, February 08, 2013 9:29 PM
     
      Has Code
    Sign In to Vote
    0
     
    declare @s varchar(200)='1126_Construction_037_Hardwood veneers'
 

DECLARE @myXML AS XML  = N'<H><r>' + Replace(@s, '_', '</r><r>') + '</r></H>'
 
SELECT distinct Vals.id.value('(/H/r)[2]', 'NVARCHAR(50)')  AS val

FROM @myXML.nodes('/H/r') AS Vals(id)

    I would go with this one since it is not dependent on the text maintaining the pattern you have given but will simply return the word after the second '_'. Great job Jingyang Li!

    Pérez