CODEWITHSUNDEEP
mysqlsql
s_p
s_p

Reputation: 4693

mysql select with 2 different substrings and single WHERE from 1 column

i am trying to pull data from sql with several conditions from a single column
how can i get this to work?

SELECT RIGHT(productID, 2) AS 'a', SUBSTRING(productID,1,2) AS 'b', productID 
FROM products 
WHERE `group`='$one' 
  AND `a` LIKE 'AA%' 
  AND `b` LIKE '$two'

i am trying to get the first 2 letters of the row and last 2 letters from the same row as well as checking if group=$one but get this error

    Unknown column 'b' in 'where clause'

Upvotes: 2

Views: 1212

Answers (4)

Jermin Bazazian
Jermin Bazazian

Reputation: 1970

I created a simple table (sotest for StackOverFlow Test table) and added a VARCHAR column to it called col1. The code below worked for me.

SELECT RIGHT(col1, 2) AS a, SUBSTRING(col1,1,2) AS b, col1 as col FROM sotest  Having a like 'in%' and b like 'te%'

The return is as bellow

|  a |  b  |     col      |
|'in'| 'te'| 'test_jermin'|

Upvotes: 1

xQbert
xQbert

Reputation: 35343

SELECT RIGHT(productID, 2) AS 'a', SUBSTRING(productID,1,2) AS 'b', productID 
FROM products 
WHERE `group`='$one' 
GROUP BY productID
HAVING
  a = 'AA' 
  and b LIKE '$two'

No need for the like it's two positions % increases execution.

Upvotes: 1

hjpotter92
hjpotter92

Reputation: 80657

That is because of your error in RIGHT function call, you have a misplaced parenthesis. Change the statement of SELECT to this:

SELECT RIGHT(productID, 2)

Upvotes: 0

Marc B
Marc B

Reputation: 360842

SELECT RIGHT(productID), 2)
                      ^---extra bracket

you're terminating the function before you specify how many characters to extract.

Upvotes: 0

Related Questions