remove a number

can you please clarify the final end result for the given data?
as you have several 1 and even more 0 in the 100000010 , for example...

Hi

we have
Corp_pn
10000000
10000001
down to
10125411

we would like to take the 0 out after the first 1 so it would be
corp_pn
1000000
1000001
down to
1125411

Hope this helps
Thanks

if the field is numerical:

select yourcol
  , yourcol - 9000000 
  from yourtable

Select allOpen in new window

For updating and select only where replacement required at frOnt of data

-- Select STRING
SELECT '1' + substring(yourcol, 3, len(corp_pn )-2)
FROM TABLENAME
WHERE SUBSTRING(corp_pn, 1, 2) = '10' 
UNION
SELECT corp_pn
FROM TABLENAME
WHERE SUBSTRING(corp_pn, 1, 2) <> '10' 
 
--Update
-- For a string 
UPDATE TABLENAME
set corp_pn = '1' + SUBSTRING(corp_pn, 3, LEN(corp_pn)-2) 
WHERE SUBSTRING(corp_pn, 1, 2) = '10' 
 
--For a numeric field
UPDATE TABLENAME
set corp_pn = cast('1' + SUBSTRING(CAST(corp_pn AS VARCHAR(10)), 3, LEN(corp_pn)-2) as int)
WHERE SUBSTRING(CAST(corp_pn AS varchar(10)), 1, 2) = '10' 

Select allOpen in new window