Select a matching rows which exist in particular string

Question

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

The way I would do it is to split the string into its component words using this[/url].

From there, you can do a direct match.

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 2

Quick solution

😎

USE tempdb;

GO

SET NOCOUNT ON;

GO

DECLARE @strname varchar(50) = '3 April 15 abcd Oh rrrrrrrAAAAdd HJHJG';

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,'abcd ER')

,(2,'abcd AS')

,(3,'abcd Oh')

,(4,'xyz TR' )

,(5,'azs WS' )

,(6,'abcd O' )

,(7,'OP trx' );

SELECT

SD.SD_ID

,SD.SD_TEXT

FROM @SAMPLE_DATA SD

WHERE CHARINDEX(SD.SD_TEXT,@strname,1) > 0;

Results

SD_ID SD_TEXT

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

3 abcd Oh

6 abcd O

Edit: Code correction

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 3

If performance isn't a concern simply run

select * from t

where @strname like '%'+text+'%'

Do expect full table scan.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 4

I should have read the question more closely. My idea would not work because the search strings include spaces.