SUBSTRING and CAST Help Required

• atomic94w

  SSC Rookie

  Points: 27

  More actions

  December 5, 2013 at 2:29 am

  #279757

  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

• Paul Keys

  SSC Eights!

  Points: 866

  More actions

  December 5, 2013 at 2:53 am

  #1670969

  Hi,

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

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

• sqlmunkee

  SSCommitted

  Points: 1949

  More actions

  December 5, 2013 at 3:52 am

  #1670981

  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]

• atomic94w

  SSC Rookie

  Points: 27

  More actions

  December 5, 2013 at 4:43 am

  #1670995

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

• atomic94w

  SSC Rookie

  Points: 27

  More actions

  December 5, 2013 at 4:44 am

  #1670996

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