SELECT Statement with substr in the WHERE clause

Question

I have an example table with three fields.

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

FieldA has a fixed length of 9. I have not designed this table, and some applications already use 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 can not achieve the desired result. When I try to search with paramA 12 and paramB '' , I get 2 results:

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

because, obviously, it corresponds to 12% , and this is not what I want. I want the parameters to match the correct row index.

If I look for paramA = '12' and paramB = '' , then it should not have a result. To get the fields ( FieldB , FieldC ), I need the correct values paramA = '123' and paramB = '456' so that it returns XYZ and John . If I want to return James , then I have to give paramA = '456' and paramB = '345'

How can I build the SQL statement correctly for this? Any ideas? 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' 

Answer 3

You didn’t make it clear why you even allowed paramB to be empty in your input check, but to get the exact behavior that you described, you can simply check this case, that is:

 WHERE @paramA!='' AND @paramB!='' AND FieldA LIKE Concat(@paramA, '%', @paramB) 

Answer 4

Also: for example, a classification table: houses in America, land in America, etc.

 mysql_query("select classification from properties where LEFT(classification, 5) = 'Homes'"); 

Result: Home