Post reply

Error converting the varchar value to int

  • 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.

  • April 8, 2010 at 3:19 pm

    #1148683

    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]

  • April 8, 2010 at 6:16 pm

    #1148729

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

  • April 8, 2010 at 9:03 pm

    #1148774

    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

    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]

  • April 9, 2010 at 7:12 am

    #1149060

    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!!

  • April 9, 2010 at 8:47 am

    #1149177

    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://qa.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]

  • April 11, 2010 at 9:56 am

    #1149717

    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

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

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