Post reply

String breakup

  • January 4, 2012 at 1:11 pm

    #249133

    Hi,

    I have following string to parse and I want to get only 'abcde' out of it, how do I do it. basically after ; I should look for : and get all the text until I hit space. How can I do it.

    abc:de;fgh:abcde

    Thanks.

  • January 4, 2012 at 1:23 pm

    #1428630

    This would give you the rest of the char string after the first colon that appears after the first semi-colon:

    declare @init varchar(50) = 'abc:de;fgh:abcde';

    select substring(@init, charindex(':', @init, charindex(';', @init)) + 1, len(@init) - charindex(':', @init, charindex(';', @init)));

    Just replace the @init with what you're actually parsing ... I would assume a column.

    Be warned that SQL Server is not very efficient with string parsing. If you are going to be doing a lot of parsing I would highly suggest looking into CLR solutions.

    └> bt


    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • January 4, 2012 at 1:29 pm

    #1428633

    Actually, I need to read better. This would give you the string to the next space, or it would give you the string to the end of the value in the case of there not being a space ...

    declare @init varchar(50) = 'abc:de;fgh:abcde f';

    select case charindex(' ', @init, charindex(':', @init, charindex(';', @init)))

    when 0 then substring(@init, charindex(':', @init, charindex(';', @init)) + 1, len(@init) - charindex(':', @init, charindex(';', @init)))

    else substring(@init, charindex(':', @init, charindex(';', @init)) + 1, charindex(' ', @init, charindex(':', @init, charindex(';', @init))) - charindex(':', @init, charindex(';', @init)))

    end;

    └> bt


    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • January 4, 2012 at 1:48 pm

    #1428647

    Perfect !! thank you.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply