Our Training Programs

All contents are copyright of their authors.

in dynamic sql removing leading zeros

in dynamic sql removing leading zeros

vijay reddy

10yAsked Jul 11, 2016, 1:22 PM1.4k

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

Know the answer? Post it — somebody with the same question will find it here.

It is the same account you read, post and publish with — and you will come straight back to this page.

Daril4y ago
zeros

1 1

2 100

3 000100

4 100100

5 00.001

6 01.001

So if you have a table with zeros as shown above .

The LTRIM removes the leading spaces . And the RTRIM removes the trailing space

SO If we execute the below query

We will get the output as:

We can also do this by casting the specific column as float.
Raja T10y ago
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 Sreeram10y ago
Hi Vijay,
Please have a look at the following
http://stackoverflow.com/questions/662383/better-techniques-for-trimming-leading-zeros-in-sql-server