SQL QUERY With CONVERT And VARCHAR

Yesterday, I faced one problem which I would like to highlight for you.

One of my testing users generated RDLC report and he was very shocked to find that his tested email address was truncated when he viewed in the report and in "Export to Excel" functionality.

As this report is working for last 2 to 3 years and it's working for multiple uses, so I thought the following points will help me out.

  • Email address might be wrong for this employee.
  • There must be some substring function which were written in column for Email Address in RDLC report.
  • Debugging the code level whether a substring was used.

But, I was suprised to identify the root cause. For Email address, the code in SQL was written as SELECT CONVERT(VARCHAR, EmailAddress) which was truncating it and giving us the wrong result.

Written some dummy email address for illustration.

If you try out -

select CONVERT( VARCHAR, '987654321.987126515151@abcdef.com')

It will return an output as - "987654321.987126515151@abcdef." , Thus, the maximum lenght is consider here which is 30 characters.

So just for information, always use Convert(Varchar(Max), <ColumnName>) or Convert(Varchar(<size>), <ColumnName>) to get the result correct.

Hope it will help you out .

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now