sql server - Find string length in sql without starting of zero -
this question has answer here:
i have problem, uploading employee code , account number excel(.csv) file, , check employee code , update or insert account number corresponding employee code. if employee code start "0" csv file not consider 0's , send remaining character, when check in sql not match table data (employee code).
i have plan match them, don't know how 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'
if spaces not expected, close tried need:
declare @employeecodedb varchar(max)='00mj300'; select replace(ltrim(replace(@employeecodedb, '0', ' ')),' ','0')
that is, replace 0
s spaces, (ltrim
) remove leading spaces, , replace spaces 0
s (so, original 0
s weren't in leading position reverted back)
result:
mj300
if spaces may exist in string , they're not required, shouldn't magically turn 0
s, can replace inner @employeecodedb
yet replace: replace(@employeecodedb,' ','')
Comments
Post a Comment