SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SUBSTRING and CAST Help Required


SUBSTRING and CAST Help Required

Author
Message
atomic94w
atomic94w
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
Paul Keys
Paul Keys
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1877
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
sqlmunkee
Right there with Babe
Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)

Group: General Forum Members
Points: 741 Visits: 286
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
atomic94w
atomic94w
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 2
Thank you Paul, just tried it out and works well.
atomic94w
atomic94w
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search