Select a matching rows which exist in particular string

• fanindrabhortakke

  Old Hand

  Points: 349

  More actions

  April 14, 2015 at 5:59 am

  #302943

  Hi,

  Suppose I have string like

  DECLARE @strname varchar(50) = 'i_ncm_ai04';

  DECLARE @SAMPLE_DATA TABLE

  (

  SD_ID INT NOT NULL

  ,SD_TEXT VARCHAR(10) NOT NULL

  );

  INSERT INTO @SAMPLE_DATA(SD_ID,SD_TEXT)

  VALUES

  (1,'i_n0')

  ,(2,'i_nccc')

  ,(3,'i_ncm')

  ,(4,'i_ncm_pp' )

  ,(5,'i_ncm_ar' )

  ,(6,'i_ncm_an' )

  ,(7,'i_ncm_ai05' )

  ,(8,'i_ncm_a');

  how can I search a ID's which are exist in my string.

  result should be,

  3 i_ncm

  8 i_ncm_a

  Thanks,

  Fanindra

• Phil Parkin

  SSC Guru

  Points: 247180

  More actions

  April 14, 2015 at 6:05 am

  #1790757

  select * from @SAMPLE_DATA sd

  where @strname like '%' + sd.SD_TEXT + '%'

  ---

• Phil Parkin

  SSC Guru

  Points: 247180

  More actions

  April 14, 2015 at 6:06 am

  #1790758

  Actually, this version should be faster:

  select * from @SAMPLE_DATA sd

  where @strname like sd.SD_TEXT + '%'

  ---