Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SUBSTRING and CAST Help Required Expand / Collapse
Author
Message
Posted Thursday, December 5, 2013 2:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 5, 2013 4:41 AM
Points: 3, Visits: 2
Hello,

I have a unique column in a database called ID as varchar
This column contains data in such format as:
ABC124329587
ABC124329789
ABC434329587
ABC994329587


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
SELECT SUBSTRING(ID, 3, len(ID)) AS ID FROM table

However when trying to convert this as well to a BIGINT I am receiving a number of errors, this is what I have tried:
SELECT SUBSTRING(CAST(ID AS bigint),3,LEN(ID)) AS ID FROM table

Any help much appreciated.

Regards
Post #1519917
Posted Thursday, December 5, 2013 2:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 4:46 AM
Points: 253, Visits: 1,225
Hi,

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

SELECT CAST(SUBSTRING(ID,3,LEN(ID)) AS bigint) AS ID FROM table
Post #1519928
Posted Thursday, December 5, 2013 3:52 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 7:30 AM
Points: 386, Visits: 235
Agreed - It looks like your logic gets you something which isn't possible. Hence the error




sqlmunkee
Bringing joy and happiness via SQL Server since 1998
Post #1519942
Posted Thursday, December 5, 2013 4:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 5, 2013 4:41 AM
Points: 3, Visits: 2
Thank you Paul, just tried it out and works well.
Post #1519962
Posted Thursday, December 5, 2013 4:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 5, 2013 4:41 AM
Points: 3, Visits: 2
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...
Post #1519963
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse