Tag: MSSQL

Long character fields from database weird in PHP

When you are returning longer text from a query in PHP, like from a varchar(MAX) or such you can get weird characters at the end.  The problem is that the ODBC connection, which I used to query the MS SQL database, has a default field bytes limit of 4096 chracters.  This is controlled by the odbc.defaultlrl setting in the php.ini file.  The solution that I found was to override this setting in the page where you need the long text with a line like this.

ini_set(‘odbc.defaultlrl’,65536);

Supposedly you can use the functionodbc_longreadlen setting, but I was not able to get it to work.  You can find more info here and here.  The default value for this is 4096, so 65536 may be overkill, but you can find what fits.  When I tried 0, which is supposed to set it to passthrough all the text it didn’t work at all.

For the curious lrl = long read length

 

Order characters numerically

It may be simple, but this SQL will order items numerically (1,2,3,10,20) instead of alpha (1,10,2,20,3).


SELECT ItemID, ItemName, ItemNum, CASE WHEN Len(ItemNum) = 0 THEN 'Z' ELSE SPACE(10- Len(ItemNum)) + ItemNum END OrderCol
FROM table
ORDER BY OrderCol