find string in another string

• christophe.bernard 47659

  Ten Centuries

  Points: 1169

  More actions

  February 4, 2016 at 3:34 am

  #315622

  Hello all,

  i work with sql server 2008 and i have a little problem about a work i must to realize ...

  Imagine this sample :

  declare @tblDatas table(id int, completeName varchar(200), info varchar(100))

  INSERT INTO @tblDatas values(1, 'name 1, name 2', 'toto'), (2, null, ''), (3, 'name 3', 'toto')

  SELECT * FROM @tblDatas

  declare @tblPerson table (idPers int, namePers varchar(20))

  INSERT INTO @tblPerson values(9684, 'name 1'), (4, 'name 2'), (8503, 'name 3')

  SELECT * FROM @tblPerson

  I have two table, one represent all my datas and the second table is the person list.

  in @tblDatas i have the field completeName who is the reference for @tlbPerson

  i can have several name separate by a coma => that's work 🙂

  now, I must to modifiy a store procedure for to filter @tblDatas follow by the person in parameters

  the parameters pass to the store procedure will be a string as this for example :

  declare @filemanager varchar(100) = '9684,4'

  these id are the personId locate in @tblPerson

  I've made a script base on this string for to get the list of users as a result => @search = 'name 1, name 2'

  Now, i would like to know how i can filter my @tblDatas based on a string as @search

  The result for this sample will be :

  id completeName info

  ----------------------------------

  1 name 1, name 2 toto

  i've try to use contains but that's doesn't work because the column is not set to an index ..

  Any idea ?

  thanks for your help guys

  thanks for your time

  christophe

• Sean Lange

  SSC Guru

  Points: 286573

  More actions

  February 4, 2016 at 7:20 am

  #1856528

  christophe.bernard 47659 (2/4/2016)

  ---

  Hello all,

  i work with sql server 2008 and i have a little problem about a work i must to realize ...

  Imagine this sample :

  declare @tblDatas table(id int, completeName varchar(200), info varchar(100))

  INSERT INTO @tblDatas values(1, 'name 1, name 2', 'toto'), (2, null, ''), (3, 'name 3', 'toto')

  SELECT * FROM @tblDatas

  declare @tblPerson table (idPers int, namePers varchar(20))

  INSERT INTO @tblPerson values(9684, 'name 1'), (4, 'name 2'), (8503, 'name 3')

  SELECT * FROM @tblPerson

  I have two table, one represent all my datas and the second table is the person list.

  in @tblDatas i have the field completeName who is the reference for @tlbPerson

  i can have several name separate by a coma => that's work 🙂

  now, I must to modifiy a store procedure for to filter @tblDatas follow by the person in parameters

  the parameters pass to the store procedure will be a string as this for example :

  declare @filemanager varchar(100) = '9684,4'

  these id are the personId locate in @tblPerson

  I've made a script base on this string for to get the list of users as a result => @search = 'name 1, name 2'

  Now, i would like to know how i can filter my @tblDatas based on a string as @search

  The result for this sample will be :

  id completeName info

  ----------------------------------

  1 name 1, name 2 toto

  i've try to use contains but that's doesn't work because the column is not set to an index ..

  Any idea ?

  thanks for your help guys

  thanks for your time

  christophe

  You have several challenges here. The first is the major problem that you are storing delimited data. This violates 1NF and causes serious amount of pain. If at all possible, don't do this.

  If you are stuck with this data you will have to split your strings on the delimiter. See the article referenced in my signature about splitting strings.

  I could help you here but sadly the output just doesn't make a lot of sense to me. You seem to be looking for the rows in @tblPerson but the output has some data also from @tblDatas. You only have the person from the first value in your delimited string, not sure why that is. Then you have some data from tblDatas but there is no explanation about how those two tables are related. If you can explain the relationship and what you are trying to do here we can help.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Sergiy

  SSC Guru

  Points: 110206

  More actions

  February 10, 2016 at 5:09 pm

  #1857898

  christophe.bernard 47659 (2/4/2016)

  ---

  Now, i would like to know how i can filter my @tblDatas based on a string as @search

  Any idea ?

  thanks for your help guys

  christophe

  Try to put your data into a relational database.

  It should help a lot.

  _____________
  Code for TallyGenerator

• Ed Wagner

  SSC Guru

  Points: 287024

  More actions

  February 10, 2016 at 5:19 pm

  #1857900

  The splitter Sean mentioned is certainly a good one - I rely on it daily.

  However, like both other pieces of advice have said, don't store multiple columns of data in a single column so you're required to split your strings in the first place. Not only will it help you now, but it'll help avoid performance problems in the future. It'll also save your mind when you have to anything with individual parts of your consolidated columns, like say join two tables together.

  It's best to store different columns of data in different columns. In fact, I can't think of a case where it wouldn't best. I'm sure the situation is out there, but I've haven't encountered it yet.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions

• ben.brugman

  SSChampion

  Points: 13369

  More actions

  February 11, 2016 at 9:41 am

  #1858078

  As allready said, the splitter by Jef Modem is a very usefull tool in general and also for this situation.

  In the example below I have removed the spaces in the data. Why ? The spaces have two different functions; 1 is is to sepparate the ',' from the next character. 2 it is part of the name. (Or can a name start with a space ?). *)

  -----------------------------------------------------------------------------------------------

  -- Set up:

  declare @tblDatas table(id int, completeName varchar(200), info varchar(100))

  INSERT INTO @tblDatas values(1, 'name1,name2', 'toto'), (2, null, ''), (3, 'name3', 'toto')

  SELECT * FROM @tblDatas

  declare @tblPerson table (idPers int, namePers varchar(20))

  INSERT INTO @tblPerson values(9684, 'name1'), (4, 'name2'), (8503, 'name3')

  SELECT * FROM @tblPerson

  -----------------------------------------------------------------------------------------------

  --

  -- Split the rows into their respective names.

  -- Remark all spaces have been removed from CompleteName

  --

  SELECT id, Item as names, info, CompleteName,ItemNumber into #tmp_datas FROM @tblDatas

  CROSS APPLY master.dbo.DelimitedSplit8K(CompleteName,',') as pg1

  select * from #tmp_datas

  select * from #tmp_datas T Join @tblPerson P on t.names = P.namePers

  -- Result:

  -- idnamesinfoCompleteNameItemNumberidPersnamePers

  -- 1name1totoname1,name2 1 9684name1

  -- 1name2totoname1,name2 2 4 name2

  -- 3name3totoname3 1 8503 name3

  drop table #tmp_datas

  -- Remarks:

  -- I have removed the spaces in the 'strings'.

  -- This can be done in line, or whatever suits you.

  -- The function DelimitedSplit8K can be found clicking on Splitter (Jeff Moden see message Sean)

  I hope this helps with your problem.

  Keep us informed how this worked.

  Ben

  *)

  If the spaces are 'realy' part of the data, the first space from the name can be removed using a different technique, or each comma followed by a space can be replaced by a comma (inline).

• christophe.bernard 47659

  Ten Centuries

  Points: 1169

  More actions

  February 16, 2016 at 4:36 am

  #1858941

  Hello ben.brugman ,Sean Lange ,Sergiy, Ed Wagner

  After talking with my responsible of project and explain the difficult of this problem we have decide to migrate our table.

  And stop to save several user name in one column 🙂

  @ben.brugman => thanks for this sample I keept it presiously

  thanks for your time that's great to find any help here

• Ed Wagner

  SSC Guru

  Points: 287024

  More actions

  February 16, 2016 at 6:21 am

  #1858958

  christophe.bernard 47659 (2/16/2016)

  ---

  Hello ben.brugman ,Sean Lange ,Sergiy, Ed Wagner

  After talking with my responsible of project and explain the difficult of this problem we have decide to migrate our table.

  And stop to save several user name in one column 🙂

  @ben.brugman => thanks for this sample I keept it presiously

  thanks for your time that's great to find any help here

  Thanks for the feedback. Kudos on the decision to split your data into individual columns. I think you'll find that this simplifies your life greatly.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions