find whether this string is a substring of another row

• from corporate

  SSC Veteran

  Points: 279

  More actions

  October 15, 2009 at 1:35 am

  #135776

  Hi my data is like as follows...

  subject_key username address reports_ran

  1 abc address1 [rep1]

  2 abc address1 [rep1][rep2]

  3 def address2 [rep1]

  4 def address2 [rep2]

  5 ghi address3 [rep3]

  on above set of data when we look at first two records for same user name, for same address we have two reports ran in two instances. that why we have two different subject_key values. but when look at reports_ran data [rep1] is substring of [rep1][rep2]. so i want to make a flag there.

  as a result i am expecting the data like follows in any of the case

  case1:

  subject_key username address reports_ran

  2 abc address1 [rep1][rep2]

  3 def address2 [rep1]

  4 def address2 [rep2]

  5 ghi address3 [rep3]

  case 2:

  subject_key username address reports_ran dup_flag

  1 abc address1 [rep1] Y

  2 abc address1 [rep1][rep2] N

  3 def address2 [rep1] N

  4 def address2 [rep2] N

  5 ghi address3 [rep3] N

  please help me to resolve this. per my knowledge we can do this using a stored procedure by having some loop, but in my case i need to get this in sql statement.

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  October 15, 2009 at 1:39 am

  #1066112

  It will be a simple case of joining the table to itself on username and patindex('%'+table1.rep+'%',table2.rep) > 0

  ---

  
  Clear Sky SQL
  My Blog[/url]

• from corporate

  SSC Veteran

  Points: 279

  More actions

  October 15, 2009 at 1:45 am

  #1066115

  Hi,

  thanks for you reply. could you please help me to understand how it works. will it filter the rows or to get a flag?

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  October 15, 2009 at 1:57 am

  #1066117

  Dont think that im being awkward for the sake of it , just helping you to help yourself.

  Have you looked up Patindex in BOL (Books On Line) to see what it returns ?

  ---

  
  Clear Sky SQL
  My Blog[/url]

• arun.sas

  SSChampion

  Points: 11831

  More actions

  October 15, 2009 at 2:36 am

  #1066131

  Try this

  create table #temp

  (

  keys int,

  users varchar(10),

  addr varchar(50),

  report varchar(50)

  )

  insert into #temp

  select 1,'abc','add-1','rep-01'

  union all

  select 2,'abc','add-1','rep-01 rep-02'

  union all

  select 3,'bcd','add-2','rep-01'

  union all

  select 4,'bcd','add-2','rep-02'

  union all

  select 5,'cde','add-2','rep-01'

  union all

  select 6,'cde','add-2','rep-02'

  select users,addr,report,

  replace(report,'rep-01','') trims,

  (case when ((replace(report,'rep-01',''))='') or ((replace(report,'rep-01','')) = report) then 'N' else 'Y' end)dups

  from #temp