1

I came across an issue today, and while I was able to fix it, I am unsure what caused the issue to happen.

I have a column that is Varchar(20). It is used to store just year data (2010, 2011, etc.) it is being converted to an Int data type, but when the command below is ran, an error code happens.

ALTER TABLE mytable ALTER COLUMN1 int NULL

The error displayed is: Conversion failed when converting the varchar value '2010. ' to data type int.

There are 3 rows in the table with the value of 2010 in Column1, but NO rows with '2010. ' (period and 10 spaces) as a value. I ran

select len(Column1) from MyTable where Column1 like '%2010%'

I got 2 rows with a length of 4, and 1 with a length of 5. After updating the row with 5 length to have 2010 as the value, the table could be altered.

I am curious where the period and spaces came from here. Also curious how 1 character length increase results in a period and 10 spaces. I am thinking something along the lines of a carriage return or line feed, but I can't find any data to back that up, its just a hunch.

1

You'd need to determine exactly what character was in the original string. Hopefully you have a copy of the original somewhere. I'm guessing it wasn't actually a full stop even if it displayed as one. This query may help

declare @s table
(
    col varchar(20) not null
);

insert @s(col) values ('2010'), ('2010.   ');

;with Numbers as
(
    select top(20)
        rn = ROW_NUMBER() over (order by (select 1))
    from sys.objects as o
)
select
    s.col,
    n.rn,
    SUBSTRING(s.col, n.rn, 1),
    ASCII(SUBSTRING(s.col, n.rn, 1)),
    UNICODE(SUBSTRING(s.col, n.rn, 1))
from @s as s
cross apply Numbers as n
where n.rn < = DATALENGTH(s.col)
and s.col like '%2010%'
order by
    s.col,
    n.rn;

The CTE is just a quick numbers table statically sized to the column's declared width. It will return one row per possible character position in the input data.

The main query uses the CTE's output as an ordinal index to extract one character at a time.

1

LEN() in most SQL distributions do not include spaces. You would want to use something like DATALENGTH() for that. https://stackoverflow.com/questions/1151693/len-vs-datalength-in-sql-server-2005 As for where the '.' and spaces came from, we would need more information like how is that field being populated? What app, directly via SQL, etc.
Also, it would be helpful to know what distribution you are using. I assumed SQL Server.

  • Thanks for the response! The data in that column is populated through the application we develop that interfaces with SQL, so its not entered directly into SQL. I would have to check with our developers to know the exact process how that happens in case something in that process is contributing. We are using MS SQL Server – Gloeffler Jan 10 at 21:44
  • I can't speak for your developers but I can say, unless they can identify a specific bug that would put the value in like that, you might not ever know. When troubleshooting stuff like this, the first step is to reproduce the issue. Since you have almost all good records, I would bet that is going to be tricky. It's possible that it was a bug and was fixed but the data was never cleaned up. – Shooter McGavin Jan 10 at 21:48

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.