CODEWITHSUNDEEP
mysqlsqlvarcharsqldatatypesbigint
Lucia
Lucia

Reputation: 1

Convert VARCHAR to BIGINT

I'm trying to join two tables on two columns with different data types. One is varchar and one bigint. When executing the query I get an error saying 'Error converting data type varchar to bigint.

CASE WHEN c.vcCompanyNo NOT LIKE '%[^0-9]%' 
     THEN CAST (c.vcCompanyNo AS BIGINT)
     ELSE 0
     END
FROM Client c
LEFT JOIN Nzbn n ON c.vcCompanyNo=n.SourceRegisterUniqueIdentifier
WHERE c.daTerminationDate IS NULL

Where did I go wrong? Thanks!

Upvotes: 0

Views: 1160

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

In MySQL, you can use regexp instead of like. For all digits:

(CASE WHEN c.vcCompanyNo REGEXP '^[0-9]+$' 
      THEN CAST(c.vcCompanyNo AS UNSIGNED)
      ELSE 0
 END)

Note that MySQL does not support BIGINT in CAST (why? I have no idea).

MySQL also supports implicit conversion with no errors. So this might more simply do what you want:

(c.vcCompanyNo + 0)

This converts the leading digits to a number value. If the first character is not a digit, then it returns 0.

Upvotes: 1

Shadow
Shadow

Reputation: 34231

The like operator does not support regular expressions, only _ and % operators.

You need the rlike operator instead. You also seem to be doing a double negation (not like and [^...]). rlike '[0-9]' should do the trick.

Upvotes: 0

Related Questions