select query without 0 values

yesubabu k

9y873

sir i have employee database in that employee id stored with 0 like 000empde4,00empde5,

0000empde5

then i want to retrive the data without prefix 0

cau you anybody help me

8 Replies

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

Manish Kumar9y ago
I thought 000 is fixed and Yes my answer will not work if there is less 0 or more 0 and your answer is working in all condition. Thanks Nitin for your correction

Here is another solution.

declare @name varchar(20)
set @name='00empid0102 '
select substring(@name,CHARINDEX('emp',@name),len(@name))
+1
Nitin Sontakke9y ago
@Manish,

There could be any number of 0s before 'empid'.
0
Manish Kumar9y ago
Try this

declare @name varchar(20)
set @name='000empid0102 '
select substring(@name,4,len(@name))
+1
Bryian Tan9y ago
@yesubabu k. You posted the a similar question at http://www.c-sharpcorner.com/Forums/need-not-prefix-0-values , check out the answer there too
+1
Nitin Sontakke9y ago
Try second reply...
+1
Nitin Sontakke9y ago
Or using following if you always have 'emp' after any number of 0s.

select substring(employeeId, charIndex('emp', employeeId), 100) employeeId
from employeeTable
+1
yesubabu k9y ago
tanque sir
but
i need only left side 0 will remove

000empid0102

sir needed only yellow color values need not red colore values

can you help me sir
+1
Nitin Sontakke9y ago
The following solution works ONLY IF 0s are always on left side of the column. Basically ALL 0s are removed from the string.

select replace(employeeId, '0', '') employeeId
from employeeTable
+1