Convert or Cast VARCHAR to INT; Update column

Question

Disclaimer: I am still learning SQL so I apologize if my question comes off as amateur-ish or is otherwise a very simple answer. I have no formal training. I am teaching myself.

The title may be a bit confusing, as I'm not entirely sure how to word this for a title.

Basically, I want to convert a column in a table thats currently VARCHAR into an INT. The column contains only numbers formatted as such:

00001
00005
02150

These are essentially ID's which will be appended to a Name column later for other purposes. If its necessary to do so, I'd also like to know how to convert the end result INT to VARCHAR for the append portion.

Here's what I have right now:

SELECT CONVERT(INT, LocNo)

It returns the results I expect but I think I need to somehow update the existing LocNo column or otherwise put it in a new column for forward use.

What should I do to achieve this?

Solution 1

Try this

UPDATE TableName
SET LocNo =  CONVERT(INT, LocNo)

If you want new column, add new column to table and then do update

ALTER TABLE TableName
ADD NewCol Int Null

UPDATE TableName
SET NewCol =  CONVERT(INT, LocNo)

When Selecting and appending to varchar you can do

SELECT CAST(LocNO As VARCHAR) + Name as NameAppended From TableName

If you want 0's back in LocNo/newCol then

 SELECT right('00000' + CAST(LocNO As VARCHAR),0) + Name as NameAppended 
 From TableName

Solution 2

UPDATE YourTable
SET LocNo =  CONVERT(INT, LocNo)

Comments

[dburges]

You know you will lose the leading zeros right?

[HABO]

Zero padding LocNo to a constant width is probably something the OP would appreciate when appending.

[AGx-07_162]

I appreciate the info and this works perfectly for me. I don't need the 0's back but I'll keep that info in mind for future use.