T-SQL Syntax
How do you right a script that will remove the first number of a series of numbers if that first number is 0? For example if the number in the field is 0420 I want it to be 420 BUT if its 1420 I dont want it to do anything.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
only change your columns name of this query...
UPDATE x
SET x.I = dv.V
FROM X
INNER JOIN ( SELECT RIGHT(I, LEN(I) - 1) AS V,
I
FROM ( SELECT '0420' AS I
UNION
SELECT '1420' AS I
) AS der
WHERE LEFT(I, 1) = 0
) dv ON x.i = dv.I
UPDATE x
SET x.I = dv.V
FROM X
INNER JOIN ( SELECT RIGHT(I, LEN(I) - 1) AS V,
I
FROM ( SELECT '0420' AS I
UNION
SELECT '1420' AS I
) AS der
WHERE LEFT(I, 1) = 0
) dv ON x.i = dv.I
the query of
Cluskittis more easy and accurate
ASKER
Thanks
------ Start of Script ---------
RepeatRemoveLeading:
update x
set n = substring ( n, 2, len(n))
where n like '0%'
if @@rowcount>0
goto RepeatRemoveLeading
RepeatRemoveTrailing:
update x
set n = substring ( rtrim(n), 1, len(rtrim(n))-1)
where n like '%0'
if @@rowcount>0
goto RepeatRemoveTrailing
------ End of Script ---------
https://www.experts-exchange.com/questions/20066274/Remove-leading-trailing-zeroes.html?sfQueryTermInfo=1+10+30+remov+trail+zero