CODEWITHSUNDEEP
sql-server
Bruce
Bruce

Reputation: 2213

sql server parsing char to int

I have two SQL server databases which use the same code.

I am getting below error:

 Error when executing tsp_parse_str_int at line 26. Error message: Conversion failed when converting the nvarchar value '1000 ' to data type int.

If you notice there is a leading space, however in my code I am removing the space before the conversion!

    SET @temp = (select replace(@temp, ' ', ''))
    SET @projid = cast(@temp as int)

Thanks, Bruce

Upvotes: 0

Views: 1362

Answers (3)

Bruce
Bruce

Reputation: 2213

I found out it was actually not a space, either a carriage return tab or new line, I got rid of by:

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

Upvotes: 0

Mattias Lindberg
Mattias Lindberg

Reputation: 2116

I tried the code below in SQL Server 2012 and it works without any trimming at all. Are you sure it is just a normal ' ' and not another hidden character?

declare @temp nvarchar(20)
declare @projid int
set @temp = '  12345    '
--SET @temp = (select replace(@temp, ' ', ''))
SET @projid = cast(@temp as int)
select @projid

Upvotes: 1

codingbiz
codingbiz

Reputation: 26386

Try this

SET @temp = (select RTRIM(@temp))
SET @projid = cast(@temp as int)

RTRIM trims the right space and LTRIM would trim the left space. To remove spaces from both the left and right at the same time LTRIM(RTRIM(@temp))

Upvotes: 1

Related Questions