Menu
I have a SQL table called "Customers". In it is a CustomerNumber field with values like "0001234567", i.e. they are made up of numbers only, but some include leading 0s. So, when I try to run something like
sqlFetch(channel=myconn, sqtable="Customers", stringsAsFactors=FALSE)
it returns my customers table where the CustomerNumber field is numeric (instead of character), and consequently I lose all those leading 0s.
Is there a way I can specify the fieldtype for a column or an alternative solution that won't truncate all my leading 0s?
211
You can get the MySQL table columns data type with the help of “information_schema. columns”. SELECT DATA_TYPE from INFORMATION_SCHEMA. COLUMNS where table_schema = 'yourDatabaseName' and table_name = 'yourTableName'.
SELECT statements An SQL SELECT statement retrieves records from a database table according to clauses (for example, FROM and WHERE ) that specify criteria. The syntax is: SELECT column1, column2 FROM table1, table2 WHERE column2='value';
Use TYPE_NAME() to Get the Name of a Data Type in SQL Server In SQL Server, you can use the TYPE_NAME() function to return the name of a data type, based on its ID. This can be useful when querying a system view such as sys. columns that returns the type's ID but not its name.
Change data types in Datasheet view Select the field (the column) that you want to change. On the Fields tab, in the Properties group, click the arrow in the drop-down list next to Data Type, and then select a data type. Save your changes.
You can control the types of columns more fully using the as.is argument, which is documented in the Details section of ?sqlFetch as well as at the linked documentation for ?sqlQuery and ?sqlGetResults.
Basically, it is either a vector of logicals or a vector of numeric or character indices specifying which columns to leave untouched. This vector will be recycled as necessary.
(Note that RODBC will clobber columns stored in the database with type.convert even if the C API correctly returns char or varchar as the data type of the column in the database. The maintainer has not responded to any of my 4-5 emails on this issue over the past year, and I have since simply used a forked version of RODBC with the needed one line modification ever since.)
83
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
