Clever Geek Handbook

Find length of string in sql without leading zero

I have a problem, I am loading employee code and invoice number from excel file (.csv) and I will check employee code and update or insert invoice number for corresponding employee code. But if employee code starts with "0" the csv file will not consider 0 and just send the remaining character, so when I check it in sql it will not match the table data (employee code).

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

I have tried below request

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


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


source to share


3 answers


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 with 0

spaces, then ( LTRIM

) remove all leading spaces, and then replace all spaces with 0

(so, all original ones 0

that were not in the leading position go back)

Result:



MJ300

If spaces can exist in your string and are not required, but they shouldn't magically turn into 0

s, then you can replace the inner one @EmployeeCodeDB

with another replacement:REPLACE(@EmployeeCodeDB,' ','')

+5


source


Solution with PATINDEX

:

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


It will find the first character that is not 0 or a space, and take the substring from that position to the end.

+3


source 


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

SELECT @offset = LEN(@EmployeeCodeDB) - LEN(@EmployeeCodeNET)
SELECT SUBSTRING(@EmployeeCodeDB, @offset+1, LEN(@EmployeeCodeNET))
+2


source






More articles:


All Articles