in dynamic sql removing leading zeros

Question

Hi i have select id name address from emp. in that id value is comming from other source like '00123' value. i want to remove leading zeros in dynamic sql query. how can i procede. id data type is varchar(8). once if i remove the leading zeros. at run time my data like 'AB123' how can i find the value like. thanks vijay

Raja T · Answer

Hi Vijay Try like DECLARE @ID INT ='000123' SELECT @ID AS ID DECLARE @ID1 VARCHAR(10) SELECT @ID1=REPLICATE('AB'4-LEN(@ID))+ CONVERT(VARCHAR@ID) SELECT @ID1 more details of replicate function http://www.dailyfreecode.com/code/replicate-function-299.aspx https://msdn.microsoft.com/en-IN/library/ms174383.aspx

Praveen Kumar Sreeram · Answer

Hi Vijay Please have a look at the following http://stackoverflow.com/questions/662383/better-techniques-for-trimming-leading-zeros-in-sql-server