locked
Split a string RRS feed

  • Question

  • User1682179836 posted

     

    Hi everyone, this issue is pretty simple (by the subject of the post) I have a string, tha could change in size, that have values that I need to show separately, lets say I have this string:<o:p></o:p>

    1 0.19 -60.29 [SERIAL-129  ] Pass<o:p></o:p>

    The "1" is the number of test, "0.19" is the first result, 60.29 is the second result [SERIAL-129  ] is the name of the machine where the product is tested and "Pass" is the result of the hole test, what I need to do is to show each one of the previous mentioned in a column, with MID, RIGHT and LEFT could be accomplished but like I said before the string could change in size, some times could be like this:<o:p></o:p>

    4 0.03 0.05 -57.97 -70.91 [SERIAL-073 ] Warning<o:p></o:p>

    or like this:<o:p></o:p>

    1 [SERIAL-137 ] Device Status :<o:p></o:p>

    So, my question is, is there a way I can check for a special character, lets say a space or "["  so I can put each value in a column?<o:p></o:p>

    Hope I make myself clear, if not don’t hesitate to ask, any help would be greatly appreciated

    Monday, April 25, 2011 9:59 AM

Answers

  • User1508394307 posted

    You can do it using substring and charindex. Example,

    declare @txt varchar(100)
    set @txt='1 0.19 -60.29 [SERIAL-129  ] Pass'
    
    select 
    substring(@txt, 1, CHARINDEX(' ',@txt, 1)) as TestNo,
    substring(@txt, CHARINDEX(' ',@txt, 1)+1, CHARINDEX(' ',@txt, CHARINDEX(' ',@txt, 1)+1)-(CHARINDEX(' ',@txt, 1)+1)) as FirstTest

    Can you do it in a stored procedure? It would be easier to handle positions of spaces.

    declare @p1 int, @p2 int, @p3 int
    declare @machine varchar(100), @msg varchar(100), @testno varchar(100)
    declare @result varchar(100)
    
    select @p1=charindex('[', @txt, 1)
     
    select @p2=charindex(']', @txt, @p1)
    select @machine=substring(@txt, @p1, @p2-@p1+1)
    select @msg=substring(@txt, @p2+2, len(@txt)-@p2-1)
     
    select @p3=charindex(' ', @txt, 1)
    select @testno=substring(@txt, 1, @p3-1)
    
    if (@p3+1 <> @p1)
    begin
    	select @result=substring(@txt, @p3+1, @p1-@p3-2)
    end 
     
    select @machine, @msg, @testno, @result

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 25, 2011 10:46 AM