CODEWITHSUNDEEP
mysql
ali hamza
ali hamza

Reputation: 7

remove zero in mysql field using join

code = 00000000005555

2nd option code = 00000000000555

hi i am try to find out same function like ltrim() of php

SELECT * FROM db1.stock JOIN db2.prodinfo ON replace(db2.prodinfo.code,0000000000,'') = replace(db1.stock.code,0000000000,'') WHERE db1.stock.InvNo ='12' and db2.prodinfo.Cat = 'super'

i rum this temporary. becuse zero might be increase and decrease i example above. i am just want to remove zero in this query

thanks

Upvotes: 0

Views: 50

Answers (1)

John Woo
John Woo

Reputation: 263713

try by casting it into numeric,

SELECT * 
FROM db1.stock JOIN db2.prodinfo ON 
          CAST(db2.prodinfo.code AS SIGNED) = CAST(db1.stock.code AS SIGNED) 
WHERE db1.stock.InvNo ='12' and db2.prodinfo.Cat = 'super'

or

SELECT * 
FROM db1.stock JOIN db2.prodinfo ON 
CAST(db2.prodinfo.code AS DECIMAL(15,0)) = CAST(db1.stock.code AS AS DECIMAL(15,0)) 
WHERE db1.stock.InvNo ='12' and db2.prodinfo.Cat = 'super'

Upvotes: 1

Related Questions