Compare all possible substring of in two strings sql server 2008

Question

I have a table with a column and value JobSkill = ".net sap lead". Now user enter the value "abap sap hana". I want to include a where condition which match exactly 3 or more continuous characters including space. In above scenario both have common "sap" substring so the condition should result in true. Below is my query. Please help. Previously I am using charindex but it does not resolve the purpose. I am using sql server 2008

SELECT Email_Id, JobSkill FROM Jobs
WHERE CHARINDEX(JobSkill, "abap sap hana") > 0

It would be better to store the skills as discrete values, rather than stuffing them all into a string and then struggling to write meaningful queries against them because string isn't a data type designed for storing multiple values. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Aug 17, 2016 at 7:06
i gone through Full Text article but I think it will work like "LIKE" statement only thing is it is efficient. Can you tell me how it will work in my case. — Anurag
– Anurag, Commented Aug 17, 2016 at 7:11

Anton · Accepted Answer · 2016-08-17 05:38:15Z

1

You need to create a function which loops through all positions of characters of String1 except the last 2, and check if String2 is like '%' + [(x,x+1,x+2)] + '%' string, where x is current position.

So for stings ('abcd acd g', 'ert acd'),

it should check

'ert acd' like '%abc%'
'ert acd' like '%bcd%'
'ert acd' like '%cd %'
'ert acd' like '%d a%'

and so on...

If like returns TRUE, break the loop.

answered Aug 17, 2016 at 5:38

Anton

2,8821 gold badge12 silver badges15 bronze badges

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

3 Comments

Anurag Over a year ago

i am working on creating this function but I doubt whether it will be a efficient technique because if there are 15 character in a string then it have to compare 12 substrings. Is there any method for substring match in sqlserver2008

Anton Over a year ago

If performance is a concern, CLR functions (which implement similar logic)are faster, but you need to have programming skills in other languages, like C#. I have also another idea but not sure if it would be faster. You can create pre-calculated table like (3,1,3),(4,1,3),(4,2,4),(5,1,3),(5,2,4),(5,3,5),(6.... first column is length, second is start position, third is end position. Then join with that table by ON Lengh(<string>) = Table.Length and then use SUBSTRING + LIKE (OR CHARINDEX) to compare. The comment is a not good place to describe it.

Anton Over a year ago

IF the length is fixed to 15, then that table should have only 12 rows

StackUser · Accepted Answer · 2016-08-17 10:44:33Z

0

Try like this,

SELECT j.Email_Id
    ,j.JobSkill
FROM Jobs j
INNER JOIN (
    SELECT LTRIM(RTRIM(m.n.value('.[1]', 'varchar(8000)'))) SearchString
    FROM (
        SELECT CAST('<XMLRoot><RowData>' + REPLACE(@Input, ' ', '</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
        ) t
    CROSS APPLY x.nodes('/XMLRoot/RowData') m(n)
    ) T ON j.JobSkill LIKE '%' + T.SearchString + '%'

answered Aug 17, 2016 at 10:44

StackUser

5,3983 gold badges29 silver badges48 bronze badges

Collectives™ on Stack Overflow

Compare all possible substring of in two strings sql server 2008

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related