SELECT Statement with substr in WHERE Clause

Question

I have here sample table with three fields.

TableA

    FieldA        FieldB        FieldC
    ======================================
    123XYZ456     XYZ           John
    124NNN333     NNN           Jenny
    232XPT124     XPT           Jade
    456XXX345     XXX           James

FieldA has a fixedlength of 9. I didn't design this table and some applications are already using it.

I want to select FieldB and FieldC with conditions against FieldA.

Using this sql statement:

SELECT FieldB, 
       FieldC
FROM   TableA
WHERE  FieldA LIKE Concat(@paramA, '%', @paramB) 

I cannot achieve my desired result. When I try to search with paramA value of 12 and paramB value of '', I get the 2 results:

    FieldA        FieldB        FieldC
    ======================================
    123XXX456     XXX           John
    124XXX333     XXX           Jenny

because obviously it matches with 12% and that is not what I want. I want that the params should match the correct index of the string.

If I search for paramA = '12' and paramB = '' then it should have no result. To get the fields (FieldB, FieldC), I need the correct values of paramA = '123' and paramB = '456' so it will return XYZ and John. If I want to return James then i have to give paramA = '456' and paramB = '345'

How could I correctly build a SQL Statement for this? Any idea? Thanks.

Answer 1

Use LEFT() and RIGHT():

SELECT FieldB, FieldC
FROM   TableA
WHERE  LEFT(FieldA,3) = @paramA 
       AND RIGHT(FieldA,3) = @paramB;

Answer 2

SELECT Field2,Field3
FROM TABLE
WHERE SUBSTR(Field1,1,3)='123' AND SUBSTR(Field1,7,3)='456'