PatIndex or CharIndex

• tina.luo

  SSC Rookie

  Points: 45

  More actions

  June 13, 2013 at 4:52 pm

  #275042

  Hi, can someone help me with PatIndex or CharIndex?

  I have a table below

  select 2011 as Year, 1111 as Count into #t

  insert into #t select 2012, 2222

  insert into #t select 2013, 3333

  -- Year is smallint type

  select * from #t where Year in (2011,2012)

  Can I use PatIndex or CharIndex or any other ways to do same thing as above result using parameter @Year below?

  declare @Yearvarchar(50)

  set @Year = '2012,2011'

  select * from #t where Year ......

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  June 13, 2013 at 4:57 pm

  #1623936

  You have what's known as (among other things) a delimited list parameter. As you've noticed, you can't shove it into an IN, it breaks.

  The easiest way to do it is to turn the delimited list into a #temp table and join it on the field(s) in question.

  The easiest way to do that is the DelimitedSplit8k function. You'll find that here:

  http://www.sqlservercentral.com/articles/Tally+Table/72993/

  I seriously recommend reading the entire article, but the part you specifically want is: Figure 21: The Final "New" Splitter Code, Ready for Testing

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA