Find a word in a String

• SQL_Surfer

  SSCrazy Eights

  Points: 8320

  More actions

  March 27, 2013 at 8:54 pm

  #394480

  How do I find a word in string? That word stands by itself

  In this example, CD is the word, it should only return row 1 row 3 and row 4 as cd appears seperately on those rows.

  Table A

  Values(varchar)

  abbc cd ef

  abcdef

  adb ef cd

  ad cd eg

• kapil_kk

  SSC-Insane

  Points: 21316

  More actions

  March 27, 2013 at 11:46 pm

  #1601453

  You can use CHARINDEX or PATINDEX for this...

  _______________________________________________________________
  To get quick answer follow this link:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• kapil_kk

  SSC-Insane

  Points: 21316

  More actions

  March 28, 2013 at 12:17 am

  #1601457

  You can also use this --

  Create table #temp

  (

  name varchar(20)

  )

  insert into #temp

  values ('aaa'),('aa cd ef'),('abcd')

  select * from #temp

  where name like '% cd%'

  _______________________________________________________________
  To get quick answer follow this link:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• sohan.fegade

  SSCrazy

  Points: 2738

  More actions

  March 28, 2013 at 12:23 am

  #1601459

  Try -

  SELECT*

  FROMTableA

  WHEREPATINDEX('%[ ]cd[ ]%', [Values]) > 0

  OR PATINDEX('%[ ]cd', [Values]) > 0

  OR PATINDEX('cd[ ]%', [Values]) > 0

• kapil_kk

  SSC-Insane

  Points: 21316

  More actions

  March 28, 2013 at 12:27 am

  #1601461

  sohan.fegade (3/28/2013)

  ---

  Try -

  SELECT*

  FROMTableA

  WHEREPATINDEX('%[ ]cd[ ]%', [Values]) > 0

  OR PATINDEX('%[ ]cd', [Values]) > 0

  OR PATINDEX('cd[ ]%', [Values]) > 0

  Hi Sohan, I tried your query but its not returning any value

  _______________________________________________________________
  To get quick answer follow this link:
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  March 28, 2013 at 1:52 am

  #1601477

  This should work..

  DECLARE@tbl_Table TABLE

  (

  Column1 VARCHAR(100)

  )

  INSERT@tbl_Table

  SELECT'abbc cd ef' UNION ALL

  SELECT'abcdef' UNION ALL

  SELECT'adb ef cd' UNION ALL

  SELECT'ad cd eg'

  SELECT*

  FROM@tbl_Table AS t

  WHEREt.Column1 LIKE 'cd %'

  ORt.Column1 LIKE '% cd'

  ORt.Column1 LIKE '% cd %'

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• sohan.fegade

  SSCrazy

  Points: 2738

  More actions

  March 28, 2013 at 2:21 am

  #1601486

  Hi Kapil, I not sure where you are going wrong, but it should work.