Solved SQL - Getting both after & before word of the keyword in a string (1 Viewer)

Rakesh935 · Oct 4, 2019

Hi,

I have a master table which has 2 columns i.e. Codata and Keyword, below:

CoData -------------------------------------------- KeyWord
BBNRM PRE UNIVERSITY COLLEGE FOR WOMEN ------------ UNIVERSITY
NOVOFLEX MARKETING PRIVATE LIMITED ------------ MARKETING
RAY LOGISTICS SERVICES PRIVATE LIMITED ------------ LOGISTICS
MANDAR LEARNING ACADEMY PRIVATE LIMITED ------------ LEARNING
REMINCO RESOURCES PRIVATE LIMITED ------------ RESOURCES

The keyword is just a split word of the Codata string.

What I am trying to achieve, two outputs:

OutPut1
PRE UNIVERSITY
NOVOFLEX MARKETING
RAY LOGISTICS
MANDAR LEARNING
REMINCO RESOURCES

Output2
UNIVERSITY COLLEGE
MARKETING PRIVATE
LOGISTICS SERVICES
LEARNING ACADEMY
RESOURCES PRIVATE

The first output is the concatenation of the keyword along with the immediate previous word of the keyword in the codata string.

Similarly, The first output is the concatenation of the keyword along with the immediate next word of the keyword in the codata string.

I tried achieving this through the combination of substring and charindex but the output was not as expected.

Request to please advise.

Thanks,
Rakesh

June7 · Oct 4, 2019

Don't think query alone can handle this. Consider:

Code:

Function GetStringParts(strS As String, strK As String, intP As Integer) As String
Dim aryS As Variant, x As Integer
aryS = Split(strS, " ")
GetStringParts = strK
For x = LBound(aryS) To UBound(aryS)
    If aryS(x) = strK Then
        If intP = 1 And x <> LBound(aryS) Then
            GetStringParts = aryS(x - 1) & " " & strK
        ElseIf intP = 2 And x <> UBound(aryS) Then
            GetStringParts = strK & " " & aryS(x + 1)
        End If
        Exit For
    End If
Next
End Function

SELECT CoData, KeyWord, GetStringParts([CoData],[KeyWord],1) AS [Output1], GetStringParts([CoData],[KeyWord],2) AS [Output2]
FROM Master;

arnelgp · Oct 4, 2019

create a function in a Module:

Code:

Option Compare Database
Option Explicit

Public Function fnkCombi(sText As String, sMainText As String, bolFirst As Boolean) As String

    Dim dict As New Collection
    Dim var As Variant
    Dim v As Variant
    Dim s As String
    Dim idx As Integer
    If Trim(sText) = "" Or Trim(sMainText) = "" Then _
        Exit Function
    var = Split(sText, " ")
    For Each v In var
        s = Trim(v)
        If s <> "" Then
            dict.Add s, s
        End If
    Next
    idx = getIndex(dict, sMainText)
    If idx > 0 Then _
        fnkCombi = Switch(bolFirst = True, dict(idx - 1) & " " & sMainText, True, sMainText & " " & dict(idx + 1))
End Function

Private Function getIndex(c As Collection, sValue As String) As Integer
    Dim i As Integer
    Dim bolFound As Boolean
    For i = 1 To c.count
        If c(i) = sValue Then
            bolFound = True
            Exit For
        End If
    Next
    If bolFound Then getIndex = i
End Function

your SQL:

Code:

SELECT yourTableName.CoData, 
yourTableName.Keyword, 
fnkCombi([CoData],[Keyword],True) AS Output1, 
fnkCombi([CoData],[Keyword],False) AS Output2
FROM yourTableName;

Solved SQL - Getting both after & before word of the keyword in a string (1 Viewer)

Rakesh935

Registered User.

June7

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread