Home > database >  Prevent text from being interpreted as a number
Prevent text from being interpreted as a number

Time:09-09

I have a PHP/SQL app that processes invoices. Recently, I had an invoice number come in that is not being processed as text, rather as a large exponential number when I do an insert/update on associated SQL tables. For example, take an invoice number that looks like this: 123E456. PHP will try to convert this to an extremely large number due to the 'E' being bookended by numbers.

I am leaning towards this being a PHP issue because when I look at the SQL being sent to the server, it is being scripted without quotes, 123E456 rather than '123E456'.

I have tried multiple ways to try and force it to be text, but nothing seems to work.

  • If I put single quotes around the string, I get double single quotes in the SQL.
  • strval() also does not work
  • the issue might be in the SQL interpreter, but not entirely sure

Right now, I am instructing my clerks to put a space between the E and the numbers, which works for now. But, I am hoping to address this specific issue in the code rather than have the clerk remember to manage it on their end.

Can anyone help with how to force this as being text in the SQL clause?

CodePudding user response:

You can use the strval() method to cast the number as a string.

$number = 123E456;
$string = strval($number);

Or just force it to cast as a string

$string = (string) $number;

CodePudding user response:

On my little experience with SQL Server, I suggest you to cast the value to Varchar(n) in the INSERT or UPDATE clause. Example:

    INSERT INTO DATABASE_NAME (COLUMN1,COLUMN2) VALUES (CAST(COLUMN1_VALUE AS VARCHAR(50)),COLUMN2_VALUE)    
  • Related