SQL search for complex string within a row

Question

Ok so I have a database row with a specified string in for example i am here.

I want to know how I could match this row (in a T-SQL query) if for example my input was hello i am here in this bright room.

To be clearer and get a better answer hopefully, here is a rough example:

Table:

1 | i am there |
2 | i am here |
3 | i am not here |

Problem:

I have the input hello i am here in this bright room - this should return a match to row 2 above only as only row 2 contains i am here definitively whilst the others contain the characters for i am here but with subtle differences.

If anyone can help it would be much appreciated. I would like to do this all in SQL so I can create a stored procedure for the above.

This might also get you there but the answer from RedFilter is much slicker. I think you could get there with REGX via User-Defined Functions. This is an article from SQL 2005 but it is a good article. msdn.microsoft.com/en-us/magazine/cc163473.aspx — paparazzo
– paparazzo, Commented Oct 3, 2011 at 15:46

Joe Stefanelli · Accepted Answer · 2011-10-03 15:33:50Z

3

DECLARE @InputString VARCHAR(100);
SET @InputString = 'hello i am here in this bright room';

SELECT *
    FROM YourTable
    WHERE CHARINDEX(YourColumn, @InputString) <> 0;

answered Oct 3, 2011 at 15:33

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Apqu Over a year ago

Thanks @joe-stefanelli this one works well however it will also give smaller matches for example, if I just wanted to match "i am here" but also had a row in the table with just "i am" it would bring back a match for both rows. For the context I need to use it in this hopefully won't be a problem but if you know of a way to stop that behaviour it would be good to know for completeness.

Joe Stefanelli Over a year ago

@Aracas Before we could stop that behavior, we'd need to discuss how you've determined that "i am here" is a valid match but "i am" is not a valid match. What criteria do you use to make that decision?

Apqu Over a year ago

Thanks for your response @joe-stefanelli, basically the idea will be that specific sentences will be stored within rows of the table; a "input" will then be a sentence which will need to match the closest possible of all the rows therefore "i am here" is a better match than just "i am". I'm guessing that a simple SELECT TOP(1) would do the trick?

Joe Stefanelli Over a year ago

@Aracas: TOP 1 will return one result but really isn't sufficient if your requirement is to find the "closest possible" match. If that's the case, you're looking at some pretty sophisticated searching that is beyond ordinary SQL.

D'Arcy Rittich · Accepted Answer · 2011-10-03 15:31:06Z

3

declare @input as varchar
set @input = 'hello i am here in this bright room'
select *
from MyTable
where @input like '%' + MyCol + '%'

answered Oct 3, 2011 at 15:31

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

4 Comments

marc_s Over a year ago

If you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it!

paparazzo Over a year ago

The problem statement is to search on "hello i am here in this bright room"

Apqu Over a year ago

Thanks, I appreciate your help but the input is "hello i am here in this bright room" so i cannot search for "%i am here%" specifically without losing the rest of the string which may match a different row. for example "bright room" may be matched by a different row.

Apqu Over a year ago

hi @RedFilter, thanks for your update, seems a bit closer to what i was thinking however it still doesn't brink back a match? is there something i am missing?

Collectives™ on Stack Overflow

SQL search for complex string within a row

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related