0

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

Thom A
  • 88,727
  • 11
  • 45
  • 75
shem
  • 11
  • 1
  • 2
  • you are replacing chars. check TRIM function SELECT TRIM( '.,! ' FROM ' # test .') AS Result; https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql?view=sql-server-ver15 – Power Mouse Apr 26 '22 at 12:27
  • 1
    Why not just cast to a numerical data type, numbers don't have leading zeroes. Or, better yet, store your numerical data *as* a numerical data type. – Thom A Apr 26 '22 at 12:32
  • @PowerMouse: NOTE: this does not work with LTRIM. When the original string is `00001230` you want to end with `1230` i think ? – Luuk Apr 26 '22 at 12:42
  • @Luuk , LTRIM/RTRIM just trimming spaces. But it depends on SQL version , there is a function TRIM was introduced in SQL 2017. also, as my understanding , column contains mixed context. – Power Mouse Apr 26 '22 at 12:46

2 Answers2

1
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

siddharth
  • 660
  • 2
  • 6
  • 18
shem
  • 11
  • 1
  • 2
0

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
Power Mouse
  • 727
  • 6
  • 16
  • `select trim('0 ' from '000001230 ');` (Output is `123`, and not `1230`) – Luuk Apr 26 '22 at 12:44
  • again, which SQL server version you have? run this command: SELECT compatibility_level FROM sys.databases it should be 140; if you have SQL 2017+ version and compatablility level is lower check with DBA's if you can adjust it:ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 140 – Power Mouse Apr 26 '22 at 12:47
  • `SELECT compatibility_level FROM sys.databases` returns 150 (on my system) – Luuk Apr 26 '22 at 12:49
  • 1
    @luuk, i see you point, it removes trailing zerow also, in this case it would be complex string manipulation, probably with charindex etc... i updated answer – Power Mouse Apr 26 '22 at 12:50