Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2008
    Posts
    35

    Unanswered: Extract a certain sentence / group of words from a string

    Hi

    Just out of interest, is the below possible:


    Declare @v varchar(100),
    @v1 varchar(20), @v2 varchar(20),
    @v3 varchar(20), @v4 varchar(20)


    --------------------------------------
    -- List of four possible text

    set @v1 = 'This is first'
    set @v2 = 'That is second'
    set @v3 = 'This is third'
    set @v4 = 'That is fourth'

    -- Note: The vaues assigned in each of the above could also either
    -- be null or
    -- the value of any one above could be swaped with another
    -- so for .e.g. @v1 and @v4 could have there contents swaped
    --------------------------------------

    set @v = @v1 + @v2 + @v3 + @v4

    I need to to find out whether @v contains a certain value from the four possible text.
    so for e.g. I will need to find out whether @v contains the text 'This is third'.

    I have tried both charindex and patindex with substring. For e.g.

    substring(@v, patindex('%This is third%', @v),len('This is third'))

    The problem with the above is
    if @v = 'This is firstThis is third' then
    it would return 'This is first'



    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by getusama View Post
    The problem with the above is
    if @v = 'This is firstThis is third' then
    it would return 'This is first'
    No it doesn't - it returns 'This is third'

    Wouldn't this be easier anyway?
    Code:
    SELECT @searched_for
    WHERE @v LIKE '%' + @searched_for + '%'
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2008
    Posts
    35
    Thanks for the reply pootle flump

    I am sure I've tried several times yesterday but it didnt seem to have worked - I will try again.

    This search needs to be in an expression and I am not sure if the select statement can be used that way but I will try that too.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    Declare @v varchar(100),
    @v1 varchar(20), @v2 varchar(20),
    @v3 varchar(20), @v4 varchar(20)
    
    
    --------------------------------------
    -- List of four possible text
    
    set @v1 = 'This is first'
    set @v2 = 'That is second'
    set @v3 = 'This is third'
    set @v4 = 'That is fourth'
    
    -- Note: The vaues assigned in each of the above could also either
    -- be null or
    -- the value of any one above could be swaped with another
    -- so for .e.g. @v1 and @v4 could have there contents swaped
    --------------------------------------
    
    set @v = @v1 + @v2 + @v3 + @v4
    
    --I need to to find out whether @v contains a certain value from the four possible text.
    --so for e.g. I will need to find out whether @v contains the text 'This is third'.
    
    --I have tried both charindex and patindex with substring. For e.g.
    
    SELECT substring(@v, patindex('%This is third%', @v),len('This is third')), @v
    
    SELECT  @v = 'This is firstThis is third'
    
    SELECT  substring(@v, patindex('%This is third%', @v),len('This is third')), @v
    
    DECLARE    @searched_for       AS VARCHAR(20)  = 'This is third'
    
    SELECT  @searched_for
    WHERE   @v LIKE '%' + @searched_for + '%'
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2008
    Posts
    35
    Sorry for hte late reply.

    Thanks Pootle Flump

    I have retried several times using SUBSTRING but it didnt work.

    I will now have to try your way but again, I am not sure if it will work in an EXPRESSION, e.g.:


    case when @a = (SELECT @searched_for
    WHERE @v LIKE '%' + @searched_for + '%')
    then
    <...>
    end
    Last edited by gvee; 08-16-10 at 08:30.

  6. #6
    Join Date
    Jun 2008
    Posts
    35

    Exclamation

    Please would the administrator let me edit the earlier post.

    i have mistakenly added the the first two lines.

    I wrotemy note in a note pad and pasted it back here and forgot to take the first two lines off - that has nothing to do with the discussion.


    PLEASE HELP !!

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can edit yourself you know, using the EDIT button.

    I'm not sure what your question is though.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jun 2008
    Posts
    35
    Quote Originally Posted by pootle flump View Post
    You can edit yourself you know, using the EDIT button.

    I'm not sure what your question is though.
    Thanks again .

    My EDIT/DELETE button is not there. Please will you enable those for me.


    --------------------------------

    Waht I wanted to say is, because I am going to use it in a CASE statement I was not sure if the below would be possible. I am going to try it once I get back to that environment.

    case when @a = (SELECT @searched_for
    WHERE @v LIKE '%' + @searched_for + '%')
    then
    <...>
    end

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You will not have a delete button but you should have an edit one, next to Quote at the bottom right hand corner of your posts.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jun 2008
    Posts
    35
    Honestly, I do not have the button anywhere.

    I was going through all the FAQ and I have read somewhere that the administers have the ability to eanable/disable this button.

    I do not undersatnd why that may be the case.

    Please will you enable it for me.

  11. #11
    Join Date
    Jun 2008
    Posts
    35
    I now do!!

    You must have enabled it.

    Thank you so much.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nope, not me. AFAIK it is always enabled for all users.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jun 2008
    Posts
    35
    Thats pretty strange.

    But thanks anyways for all your replies.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •