Error converting the varchar value to int

Question

Post reply

Error converting the varchar value to int

Sql Student-446896

Default port

Points: 1425
More actions
April 8, 2010 at 2:31 pm

#220298

How do i rewrite this syntax? the column number is Varchar
Syntax
-------
Select
substring(number,1,4) + char(ascii('A') + convert(int,substring(number,5,2)) - 1)
from Table
Error:
Syntax error converting the varchar value ' ' to a column of data type int.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

It would greatly help if you would provide a sample of data from your 'number' field.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

BIMind SSChasing Mays Points: 628 More actions · Answer 2

just looking at your error, its picking up a space which will NOT convert to an int type.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

I have copied and pasted your code into SSMS and executed same - no error reported. Now to get tested help please post using the directions given in the article whose link is in my signature block. But for what it is worth here is my testing:

CREATE TABLE #T(number VARCHAR(7))

INSERT INTO #T

SELECT ' 234567' UNION ALL

SELECT '1 34567' UNION ALL

SELECT '12 4567' UNION ALL

SELECT '123 567' UNION ALL

SELECT '1234 67' UNION ALL

SELECT '12345 7' UNION ALL

SELECT '123456 ' UNION ALL

SELECT '1234567'

Select

substring(number,1,4) + char(ascii('A') + convert(int,substring(number,5,2)) - 1)

FROM #T

--===Results obtained:

234x

1 34x

12 4x

123 x

1234F

1234E

1234x

If you would post sample data and double check and repost the code you actually used when you recieved the error we will attempt to assist you again. If I include the value of 'ZYXWUVU' in the number field that of course does give me an error, but not the error shown in your original post. And including '1234 7'(2 blanks) does not produce an error.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Sql Student-446896 Default port Points: 1425 More actions · Answer 4

Thanks a lot for the guidance Ron. Was very helpful. The code was right. One of the values in the Number column of table had length more than 6 and i guess it was some carriage return issue. I deleted that particular row and inserted it again. The code worked just fine.

I apprciate your help very much!!

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

Sql Student-446896

For future use. To check for "hidden" characters such as a carriage return or line feed" try using this:

I added it to my tool box and found it useful.

Ooops - Do not use solution posted

Paul White NZ
There's an unfortunate typo in that function, which would result in an infinite loop!
The four quote marks should be two, or SPACE(0)

Thanks Paul

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[RemoveNonAlphaNumericCharacters](@Temp VARCHAR(1000))

RETURNS VARCHAR(1000)

/* Author: George Mastros http://blogs.lessthandot.com/ */

AS

BEGIN

WHILE PATINDEX('%[^a-z^0-9]%', @Temp) > 0

SET @Temp = STUFF(@Temp, PATINDEX('%[^a-z^0-9]%', @Temp), 1, '''')

RETURN @TEmp

END

I strongly suggest that you review this forum posting particularly the code post by Jeff Moden

http://www.sqlservercentral.com/Forums/Topic860321-338-1.aspx#bm861154

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 6

Hey Ron,

There's an unfortunate typo in that function, which would result in an infinite loop!

The four quote marks should be two, or SPACE(0) - was this dynamic SQL at some point?

Another small point - it is not collation-safe, and will miss stuff or fail to exclude stuff in some cases.

Just thought you'd like to know.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi