Way to Check Multiple LIKE without dynamic SQL

  • Atif-ullah Sheikh

    SSChampion

    Points: 12495

    Comments posted to this topic are about the item Way to Check Multiple LIKE without dynamic SQL

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • adudley

    SSC Rookie

    Points: 26

    Thanks for this, I didn't know about fn_split or the CROSS APPLY.

  • bhatta

    SSC Rookie

    Points: 25

    if you use function !!! write new fn_split_find and don't use cross apply .it's will be work more faste :-D)

    select * from @mytbl where fn_split_find(likestring,@param) >0

  • Atif-ullah Sheikh

    SSChampion

    Points: 12495

    @adudley

    you can find fnSplit on the net. just google it and you will find the code for it.

    @bhatta

    This is just an approach of performing a task without dynamic SQL. Otherwise, I think using dynamic sql in such situations will be more helpful.:-)

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • bhatta

    SSC Rookie

    Points: 25

    Atif-ullah Sheikh (9/16/2010)


    @adudley

    you can find fnSplit on the net. just google it and you will find the code for it.

    @bhatta

    This is just an approach of performing a task without dynamic SQL. Otherwise, I think using dynamic sql in such situations will be more helpful.:-)

    Absolutly 🙂

    fn_split 🙂

    http://bi-tch.blogspot.com/2007/10/sql-clr-net-function-split.html

  • gil.milbauer

    SSC Veteran

    Points: 259

    But, if it contains both strings, you'll get it in the output twice. You may need a DISTINCT.

    Also, you're not really using the CROSS APPLY functionality. This could just as easily be a JOIN (using ON instead of WHERE), since your function isn't using anything from @vTable, so it doesn't need to be re-executed for each row.

  • franp-424154

    SSC Veteran

    Points: 243

    Thanks it's good to see how others put together T-SQL and its built-in functions to come up with clever solutions. Keeps the mind nimble.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 8 posts - 1 through 8 (of 8 total)

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