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.
LVL 1
cheryl9063Asked:
Who is Participating?
 
CluskittConnect With a Mentor Commented:
If all you want is to turn it into a number, then you can do:
UPDATE table SET field=CAST(field AS int)
but this will turn 0020 into 20.

If all you want is to remove the first one:
UPDATE table SET field=RIGHT(field,LEN(field)-1) WHERE LEFT(field,1)='0'
0
 
sventhanCommented:
This script should work for you:
------ 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 ---------

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20066274.html?sfQueryTermInfo=1+10+30+remov+trail+zero

0
 
CrashmanCommented:
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
0
 
CrashmanCommented:
the query of
Cluskitt
is more easy and accurate
0
 
cheryl9063Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.