MySQL if numeric select other column

Question

Is there mysql function to check if value is number only ?

I wan't to CONCAT 2 columns, if tbl3.column2 is number then concat with other table column, if string then same table other column will do.

SELECT tbl1.column1 as Column1, tbl2.column2 as Column2, 
IF tbl3.column2 REGEXP '^[0-9]+$' THEN CONCAT(tbl3.column1, ' ', tbl4.column1) ELSE CONCAT(tbl3.column1, ' ', tbl3.column2) END IF as Combined
FROM table1 tbl1
LEFT JOIN table2 tbl2 ON tbl1.id = tbl2.id
LEFT JOIN table3 tbl3 ON tbl3.id = tbl1.id
LEFT JOIN table4 tbl4 ON tbl4.id = tbl1.id
LEFT JOIN table5 tbl5 ON tbl5.id = tbl1.id 
WHERE
tbl5.column3 = ?
ORDER BY Column1 ASC

Current code gives error:

You have an error in your SQL syntax; check the manual that corresponds
 to your MySQL server version for the right syntax to use near 'tbl3.column2 REGEXP '^[0-9]+$'
    THEN CONCAT(tbl3.column1, ' ', tbl4.column1' at line

yes Just the table names are enough to make that judgment @Strawberry — e4c5
– e4c5, Commented Feb 17, 2017 at 8:21

Bernd Buffen · Accepted Answer · 2017-02-17 08:23:23Z

2

try this please:

SELECT tbl1.column1 AS Column1, tbl2.column2 AS Column2, 
IF(tbl3.column2 REGEXP '^[0-9]+$'
    , CONCAT(tbl3.column1, ' ', tbl4.column1)
    , CONCAT(tbl3.column1, ' ', tbl3.column2)
    ) AS Combined
FROM table1 tbl1
LEFT JOIN table2 tbl2 ON tbl1.id = tbl2.id
LEFT JOIN table3 tbl3 ON tbl3.id = tbl1.id
LEFT JOIN table4 tbl4 ON tbl4.id = tbl1.id
LEFT JOIN table5 tbl5 ON tbl5.id = tbl1.id 
WHERE
tbl5.column3 = ?
ORDER BY Column1 ASC

answered Feb 17, 2017 at 8:23

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL if numeric select other column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related