Strip Leading Zero From Number Stored As Varchar

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

Solution 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 ASINT) ASVARCHAR(2)) AS NEW_STNO
FROM WOPSEGS0

Solution 2:

I think you just need a case expression to determine whether to strip the 0 or not.

declare@WOPSEGS0table (STN1 varchar(2), STCD varchar(2))

insertinto@WOPSEGS0 (STN1, STCD)
  select'00', 'AZ'unionallselect'00', 'AZ'unionallselect'01', 'CA'unionallselect'12', 'NV'select*
  , casewhenSUBSTRING(STN1,1,1) ='0'thenRIGHT(STN1,len(STN1)-1) else STN1 endas NEW_STNO
FROM@WOPSEGS0WHERE len(STN1) >0--AND SUBSTRING(STN1,1,1) = '0'

PS: This is the preferred method of posting SQL questions where you provide the DDL & DML statements required to reproduce the problem.