Find string length in sql without starting of zero [duplicate]

Question

I have a problem, I am uploading employee code and Account Number from excel(.csv) file, and I will check the employee code and update or insert the Account Number for the corresponding Employee Code. But If the employee code is start with "0" csv file will not consider the 0's and just send the remaining character, so when I check it in sql will not match with table data (Employee Code).

I have a plan to match them, but I don't know how to do.

I have Tried Below Query

declare @EmployeeCodeNET varchar(max)='MJ300'; 
declare @EmployeeCodeDB varchar(max)='00MJ300';


select LTRIM(REPLACE(@EmployeeCodeDB, '0', ''))--It should return 'MJ300'

Answer 1

If spaces are not expected, then something close to what you tried is what you need:

declare @EmployeeCodeDB varchar(max)='00MJ300';

select REPLACE(LTRIM(REPLACE(@EmployeeCodeDB, '0', ' ')),' ','0')

That is, replace all 0s with spaces, then (LTRIM) remove all leading spaces, and then replace all spaces with 0s (so, all original 0s which weren't in a leading position are reverted back)

Result:

MJ300

---

If spaces may exist in your string and they're not required, but they shouldn't magically turn into 0s, then you can replace the inner @EmployeeCodeDB with yet another replace: REPLACE(@EmployeeCodeDB,' ','')

Answer 2

Solution with PATINDEX:

declare @str varchar(max)=' 00MJ300';
SELECT SUBSTRING(@str, PATINDEX('%[^0 ]%', @str), LEN(@str))

It will find the first symbol that is not 0 or space and take substring starting at that position till the end.