locked
How to make SQL query in partial match (or leading match)? RRS feed

  • Question

  • User-1651604128 posted

    Hi,

    I have a SQL query as this:

    (Select code1, code2, code3,,, from tabl1 where code1 = @CODE1 and code2=@CODE2 and code3=CODE3..

    The code1 and code2 are only one letter, so it is exactly match, but the code3 has more than one characters, so I need to split it and do the partial match like this: code3_1 (the first character) = CODE3_1(the first character), code3_2(the second character) = CODE3_2(the second character), etc.

    For example:

    code1 = "A", code2 = "B" , code3 = "EDF"

    so if

    CODE1 = "A", CODE2="B", CODE3="E" , my query should get the result since the code3 is partial match,

    CODE1 = "A", CODE2="B", CODE3="EE" , my query should Not get the result since code3 is Not partial match,

    CODE1 = "A", CODE2="B", CODE3="ED" , my query should get the result since the code3 is partial match,

    CODE1 = "A", CODE2="B", CODE3="EDF" , my query should get the result since the code3 is fully match,

    CODE1 = "A", CODE2="B", CODE3="D" , my query should Not get the result since the code3 is not leading by D,

    I tried use "like", but it is not what I want wanted. thanks for any input

    Thursday, August 6, 2020 5:04 PM

Answers

  • User753101303 posted

    Hi,

    Maybe you tried just with WHERE ... AND Code3 LIKE @Code3 -- This is the same than Code3=@Code3

    What if you try with WHERE ... AND Code3 LIKE '%'+@Code3+'%'

    For now it seems all the sample you gave should work with LIKE. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 6, 2020 5:14 PM

All replies

  • User753101303 posted

    Hi,

    Maybe you tried just with WHERE ... AND Code3 LIKE @Code3 -- This is the same than Code3=@Code3

    What if you try with WHERE ... AND Code3 LIKE '%'+@Code3+'%'

    For now it seems all the sample you gave should work with LIKE. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 6, 2020 5:14 PM
  • User-1651604128 posted

    Hi,

    Maybe you tried just with WHERE ... AND Code3 LIKE @Code3 -- This is the same than Code3=@Code3

    What if you try with WHERE ... AND Code3 LIKE '%'+@Code3+'%'

    For now it seems all the sample you gave should work with LIKE. 

    Hi PatriceSc, thank you so much for your quick help, your codes work well, it is exactly what I wanted.

    I tried "@Code3%" with error, now I know it should be  '%'+@Code3+'%' as your code.

    much appreciated,

    Thursday, August 6, 2020 5:28 PM
  • User753101303 posted

    Yes, SQL Server doesn't work by replacing @Code3 wherever it is but like C# it uses the current context to see if this is a variable name or a string literal.

    As this is a within a string what you wrote would match if the string value itself starts with @Code3 rather than with the value stored in the @Code3 variable.

    Thursday, August 6, 2020 5:38 PM