SUBSTRING and CAST Help Required

  • Hello,

    I have a unique column in a database called ID as varchar

    This column contains data in such format as:

    [font="Courier New"]ABC124329587

    ABC124329789

    ABC434329587

    ABC994329587[/font]

    What I need to do is remove the first 3 characters ABC and then convert this to BIGINT data type.

    I have managed to remove the first three characters using SUBSTRING

    [font="Courier New"]SELECT SUBSTRING(ID, 3, len(ID)) AS ID FROM table [/font]

    However when trying to convert this as well to a BIGINT I am receiving a number of errors, this is what I have tried:

    [font="Courier New"]SELECT SUBSTRING(CAST(ID AS bigint),3,LEN(ID)) AS ID FROM table[/font]

    Any help much appreciated.

    Regards

  • Hi,

    Try the switching the order of substring/cast i.e.

    SELECT CAST(SUBSTRING(ID,3,LEN(ID)) AS bigint) AS ID FROM table

  • Agreed - It looks like your logic gets you something which isn't possible. Hence the error 🙂

    [font="Courier New"]sqlmunkee[/font]
    [font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]

  • Thank you Paul, just tried it out and works well.

  • sqlmunkee (12/5/2013)


    Agreed - It looks like your logic gets you something which isn't possible. Hence the error 🙂

    Lol agreed, couldn't make sense of it in SQL Studio however Paul's reply just made so much more sense...

Viewing 5 posts - 1 through 4 (of 4 total)

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