CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Anurag
Anurag

Reputation: 57

Compare all possible substring of in two strings sql server 2008

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

Upvotes: 0

Views: 1099

Answers (2)

StackUser
StackUser

Reputation: 5398

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 + '%'

Upvotes: 0

Anton
Anton

Reputation: 2882

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.

Upvotes: 1

Related Questions