Member Avatar for hwoarang69

How to replace string with variable in substring? btw im using vb

I have a below query that work fine but I just want to replace "ra*" with a variable. How can I do this?

SELECT DISTINCT a.[rank], b.Description
    FROM CONTAINSTABLE(myTable, *, '"ra*'") AS a
        INNER JOIN myTable AS b ON a[key] = b.ID
             ORDER BY a.rank desc;

Below is what I tried so far but it doesnt work. it treat as '"ra"' and not '"ra*"'. Any idea how can I fix this?

Dim mkey As String = "ra"
Dim query As String = "SELECT DISTINCT a.[rank], b.Description
                       FROM CONTAINSTABLE(myTable, *, '" & mKey & "*') AS a
                           INNER JOIN myTable AS b ON a[key] = b.ID
                                ORDER BY a.rank desc;"
Edited by hwoarang69
  • 2 Contributors
  • 1 Reply
  • 354 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by incomer

Dani AI

Generated

The problem is the quoting, not the asterisk. CONTAINSTABLE needs the search term wrapped in double quotes inside the single-quoted T‑SQL literal (for a prefix search you want '"ra*"' ), so your VB string must emit those inner double-quote characters. was on the right track with concatenation, but you also need to insert actual double-quote characters around the wildcarded term.

A safe, readable approach is to build the quoted search value and pass it as a parameter (avoids manual escaping and injection risks):

Dim mKey As String = "ra"
Dim searchValue As String = Chr(34) & mKey & "*" & Chr(34)   ' produces "ra*"
Using cmd As New SqlCommand("SELECT ... FROM CONTAINSTABLE(myTable, *, @search) AS a ...", conn)
    cmd.Parameters.AddWithValue("@search", searchValue)
    ' execute the command
End Using

If you must inline the text (quick fix), create the single-quoted literal that contains double quotes. Using String.Format makes the escaping clearer:

Dim mKey As String = "ra"
Dim innerQuoted As String = """" & mKey & "*" & """"   ' same as Chr(34) + mKey + "*" + Chr(34)
Dim sql As String = String.Format("SELECT ... FROM CONTAINSTABLE(myTable, *, '{0}') AS a ...", innerQuoted)

Troubleshooting tips:

  • Print or log the final string/parameter value (Debug.WriteLine or MsgBox) to confirm it actually contains the double quotes and asterisk (should look like "ra*").
  • If CONTAINSTABLE still returns no rows, verify the full-text index is present and the column is full-text indexed.
  • Prefer parameterized commands (first example) to avoid escaping mistakes and reduce injection risk.

This addresses the quoting issue that caused your variable to appear as '"ra"' instead of the required '"ra*"'.

Member Avatar for incomer

I'm no vb.net programmer, but...
did you try:

Dim mkey As String = "ra"
Dim astChar As String = "*"
Dim query As String = "SELECT DISTINCT a.[rank], b.Description
   FROM CONTAINSTABLE(myTable, *, '" & mKey & astChar & "') AS a
       INNER JOIN myTable AS b ON a[key] = b.ID
           ORDER BY a.rank desc;"

Sorry if I didn't get the quotes completely correct.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.