need not prefix 0 values

Question

Sir can anybody help for this question i have employee table with empId like 000Emp010230 000Emp012234 000Emp012234 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, 001,002, 999, etc... DECLARE @testString VARCHAR (30) = '000Emp010230' SELECT Stuff(@testString, 1, CHARINDEX( 'Emp' ,@testString) - 1, '' ) Output : Emp010230

need not prefix 0 values

Answers (4)