Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Way to Check Multiple LIKE without dynamic SQL Expand / Collapse
Author
Message
Posted Wednesday, September 15, 2010 8:17 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Saturday, November 29, 2014 3:55 PM
Points: 3,244, Visits: 5,010
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 here

Post #986854
Posted Thursday, September 16, 2010 2:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 14, 2012 5:57 AM
Points: 2, Visits: 32
Thanks for this, I didn't know about fn_split or the CROSS APPLY.
Post #987009
Posted Thursday, September 16, 2010 2:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 10, 2012 3:22 AM
Points: 3, Visits: 16
if you use function !!! write new fn_split_find and don't use cross apply .it's will be work more faste )

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

Post #987028
Posted Thursday, September 16, 2010 4:17 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Saturday, November 29, 2014 3:55 PM
Points: 3,244, Visits: 5,010
@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 here

Post #987061
Posted Thursday, September 16, 2010 4:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 10, 2012 3:22 AM
Points: 3, Visits: 16
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
Post #987075
Posted Thursday, September 16, 2010 12:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 1:29 PM
Points: 17, Visits: 133
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.
Post #987570
Posted Thursday, September 16, 2010 12:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, November 6, 2011 4:55 PM
Points: 173, Visits: 617
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.
Post #987595
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse