need not prefix 0 values

Question

Sir

can anybody help for this question

i have employee table with empId like 000Emp010230

000Emp012234

the want to retrive data from employee table no need prefix 000

only need Emp010230

Nilesh Shah · Answer

this should remove any number of leading zero Select SUBSTRING(empId PATINDEX('%[^0]%' empId+'.') LEN(empId)) From employee

Joginder Banger · Answer

Hi  all answer are good you can choose any one But I want some more clear every answer. Example 1 DECLARE @testString VARCHAR (30) = 'asdcEmp010230' SELECT Stuff(@testString 1 CHARINDEX( 'Emp' @testString) - 1 '' ) select SUBSTRING (@testString PATINDEX( '%[^0]%'  @testString+ '.' ) LEN(@testString)) --Answer One ( No column name ) Emp010230 -- Second answer. ( No column name ) asdcEmp010230 In second query create some boundation so I prefer first one.

Chetan Ranpariya · Answer

Try following query select SUBSTRING (empId 4 LEN(empId) - 3) FROM employee

Bryian Tan · Answer

Here another alternative. This soltion will still work if tomorrow the 000 prefix changed to 0000 001002 999 etc... DECLARE @testString VARCHAR (30) = '000Emp010230' SELECT Stuff(@testString 1 CHARINDEX( 'Emp' @testString) - 1 '' ) Output : Emp010230