sql server - Strip leading zero from number stored as varchar

Question

I am trying to query from a table in SQL server. I do not have the rights to update tables or create my own tables or views.

I have managed to return something very close to what I need. I need to return all the columns of a table with some of the values in a specific column changed.

What I have now:

select *, RIGHT(STN1,len(STN1)-1) AS NEW_STNO
FROM WOPSEGS0
WHERE len(STN1) > 0 AND SUBSTRING(STN1,1,1) = '0'

This is the change that needs to be made. So, if a STN1 is 08, I need to change it to 8. This code returns that result, but it omits everything that hasn't been changed ex(STN1 = 25). My thought was to somehow embed an update function inside of a select statement to return what normally would just be the new table I created by simply using the update table function.

What I have:

STN1       STCD
00         AZ
00         AZ
01         CA
12         NV

what I need:

STN1       STCD
0          AZ
0          AZ
1          CA
12         NV

I can return:

STN1       STCD
0          AZ
0          AZ
1          CA

Answer 1

Another possibility is casting it to int and then casting it again to varchar(2) (Unless you are OK keeping it as int, in which case the second cast would not be necessary).

SELECT STN1,STCD, CAST(CAST(STN1 AS INT) AS VARCHAR(2)) AS NEW_STNO
FROM WOPSEGS0