Data insert into Microsoft SQL Server

Hello,

I'm trying to insert data into a Microsoft SQL Server. Below is the query:

conn = database('DB', 'user', 'pwd'); colnames = {'"Vessel"','"Filepath"','"Date"','"Review"'};

data=report2; datainsert(conn,'"Table"',colnames,data)

%Close database connection.

close(conn);

The columns are nvarchar type with a set length. When I insert the data blank spaces are padded in the right to the size of the column. How can I avoid that? Is it issue of the odbc driver? Or the database?

Thank you, Vanessa

Answer 1

[prabhat kumar sharma]

Hi Vanessa,

The issue you're encountering with padded blank spaces in nvarchar columns is typically related to how the data is being inserted into the database. This can be influenced by the ODBC driver or the database settings. Here are steps and considerations to address this issue:

Steps to Avoid Padding in nvarchar Columns

1. Trim Data Before Insert: Ensure that the data you are inserting does not have trailing spaces.
2. Use Parameterized Queries: This can sometimes help in ensuring that the data is inserted correctly without additional padding.

Here is the refrence code you can follow:

% Establish connection to the SQL Server database
conn = database('DB', 'user', 'pwd', 'Vendor', 'Microsoft SQL Server', 'Server', 'your_server_name', 'PortNumber', 1433, 'AuthType', 'Windows');

% Define the column names
colnames = {'"Vessel"', '"Filepath"', '"Date"', '"Review"'};

% Trim the data to remove trailing spaces
dataTrimmed = cellfun(@strtrim, report2, 'UniformOutput', false);

% Insert the trimmed data
datainsert(conn, '"Table"', colnames, dataTrimmed);

% Close the database connection
close(conn);

I hope it helps!