Removing leading zeros from a string in SQL Server

Question

i have removed all leading Zeros in my column but what if the same column has a space its replaced by '0'.. Example: batch number has 000123, 0000145 but when the column has 'CO PO' its filled with 0.. o/p is CO0PO.. how do i fix this?

EXAMPLE:

SELECT REPLACE(LTRIM(REPLACE(COMPANYCODE, '0', ' ')), ' ', '0')

Removing leading zeros from a string in SQL Server 2008 R2

Answer 1

in 2017 SQL was introduced function TRIM please check example (I didn't understand if you need to keep PO/CO)

DECLARE @x VARCHAR(10) = 'PO 000127'

SELECT 
    TRIM('0 ' FROM @x) AS Result,
    CASE 
        WHEN @x like '%PO%' THEN 'PO ' + TRIM('0 ' FROM SUBSTRING(@x, 3, LEN(@x)))
        WHEN @x like '%CO%' THEN 'CO ' + TRIM('0 ' FROM SUBSTRING(@x, 3, LEN(@x)))
    ELSE TRIM('0 ' FROM @x)
    END AS Result2

UPDATED answer, after conversation with @luuke i am giving you a adjusted solution

SELECT 
CAST(
    CASE 
        WHEN @x like '%PO%' THEN REPLACE(@x, 'PO', '')
        WHEN @x like '%PO%' THEN REPLACE(@x, 'PO', '')
    ELSE RTRIM(LTRIM(@x))
    END 
    AS BIGINT) AS result

Answer 2

SELECT
 SUBSTRING(COMPANYCODE, 
    PATINDEX('%[^0 ]%', COMPANYCODE + ' '), 
    LEN(COMPANYCODE)
  ) as COMPANY_CODE
FROM TABLE1

This solved my issue in removing leading zeros. Example:my companycode column had 000123, 0000145,CO PO for which my initial LTRIM function converted it as 123,145 and CO0PO...

When i used this one it changed perfectly to 123, 145, CO PO