Way to Check Multiple LIKE without dynamic SQL

Question

Post reply

Way to Check Multiple LIKE without dynamic SQL

Atif-ullah Sheikh

SSChampion

Points: 12495
More actions
September 15, 2010 at 8:17 pm

#227793

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]

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

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

adudley SSC Rookie Points: 26 More actions · Answer 1

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

bhatta SSC Rookie Points: 25 More actions · Answer 2

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 More actions · Answer 3

@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 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

akljfhnlaflkj

SSC Guru

Points: 76202

May 18, 2016 at 7:16 am

#1879863

Thanks for the script.