Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

how to Find words with length less than or equal...

  declare @inp nvarchar(max),@data nvarchar(max)
  set @inp='You can dance, you can jive, having .... jove... jve, ...' 
  set @data = 'jeve'
  select @inp as results where @inp like '%' + @data +'%'

@inp not return any data, but i want show:

results 
=====
jive
jove
jve
share|improve this question
1  
Do you want a T-SQL solution, or a CLR solution (which would be trivial)? Do you have to do this once (favour T-SQL), or many times (favour CLR)? –  Jon Seigel May 12 '13 at 17:49
    
T-SQL please... thank you. –  Qrosh May 19 '13 at 22:10
    
If @inp = 'You can dance, you can jive, having .... jove... jve, ...jeep, ...veej' Would you want results results = jive, jove, jve, jeep, veej ? –  Thronk Oct 8 '13 at 17:32

2 Answers 2

I've never thought of SOUNDEX or DIFFERENCE as that useful, and they are certainly a bit old-fashioned these days. However we can combine it with the full-text parser, and it does in fact answer this query. Note the extra quotes in the search statement:

DECLARE @inp NVARCHAR(MAX), @data NVARCHAR(MAX)
SET @inp= '"You can dance, you can jive, having .... jove... jve, ..."' 
SET @data = 'jeve'

SELECT display_term, SOUNDEX(display_term) sdx
FROM sys.dm_fts_parser(@inp,0,0,0)
WHERE SOUNDEX(display_term) = SOUNDEX(@data)
 AND LEN(display_term) <= LEN(@data)

SELECT display_term, SOUNDEX(display_term) sdx, DIFFERENCE(display_term,@data) diff
FROM sys.dm_fts_parser(@inp,0,0,0)

There is a fairly limited use case for these functions so if you have more complex examples then they probably won't extend. Worth a mention though.

share|improve this answer
    
What would be the not olde fashioned way to do a sounds like query (soundex)? I coincidentally just learner out the algorithm that makes sounded work a couple of weeks ago and I was impressed...Maybe this would be a machine learning problem now? –  Thronk Oct 9 '13 at 0:50
1  
I was thinking something like Levenshtein Distance or some kind of CLR pattern matching, or maybe SQL 2012 SEMANTICSIMILARITYTABLE. It's not really clear what they're after. –  wBob Oct 9 '13 at 0:52

Your best bet is not T-SQL, but CLR and Regular Expressions.

Here is an article to get your started

If this is possible in pure T-SQL it will need more specific rules around what represents a match and what doesn't that your use case unfortunately did not specify.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.