Removing leading zeros from PO_Number, (using OPENQUERY)

Roman_K
Roman_K Member
in SQL DataFlows

I've tried the following 3 things to remove the leading zeros from PO_Number, (using OPENQUERY) in the workbench without success, any other suggestions please?

  1. Using CAST and LTRIM:

SELECT *
FROM OPENQUERY(ACME_CO_PRD,
'SELECT
LTRIM(CAST(CAST(fd.PO_NO AS INT) AS VARCHAR)) AS PO_No,

2. Using PATINDEX and SUBSTRING:

SELECT *
FROM OPENQUERY(ACME_CO_PRD,
'SELECT
SUBSTRING(fd.PO_NO, PATINDEX(''%%'', fd.PO_NO + ''0''), LEN(fd.PO_NO)) AS PO_No,

3. Using STUFF and PATINDEX:

SELECT *
FROM OPENQUERY(ACME_CO_PRD,
'SELECT
STUFF(fd.PO_NO, 1, PATINDEX(''%%'', fd.PO_NO + ''0'') - 1, '''') AS PO_No,

Best Answer

Answers

  • GrantSmith
    GrantSmith Coach

    have you looked into utilizing a transform on the workbench job after your query is run?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Roman_K
    Roman_K Member

    I have tried your suggestion in the following 2 ways, I am getting a workbench error: Value cannot be null (parameter name: replacement):

    1. Add a Search/Replace Transform in one step:

    Click on the "Add Transform" button and select "Search/Replace Transform" from the menu.

    In the Search/Replace Transform Editor, select the PO_No column from the "Search Column" menu.

    In the "Search Value" field, enter the regular expression to find leading zeros. Use ^0+ to match one or more leading zeros. In the "Replace With" field, leave it blank to remove the placeholder character.

    Search Value: #

    Replace With: (leave this field blank)

    2. Add a Search/Replace Transform in two steps:

    Navigate to the "Transforms" tab within the job settings.
    Click on the "Add Transform" button and select "Search/Replace Transform" from the menu.
    Configure the First Search/Replace Transform:

    In the Search/Replace Transform Editor, select the PO_No column from the "Search Column" menu.
    In the "Search Value" field, enter the regular expression to find leading zeros. Use ^0+ to match one or more leading zeros.
    In the "Replace With" field, enter a placeholder character that you can easily identify and remove later, such as #.

    Search Value: ^0+
    Replace With: #
    Save the Transform:

    Click "Apply" to save the transform.
    Add a Second Search/Replace Transform:

    Click on the "Add Transform" button again and select "Search/Replace Transform" from the menu.
    Configure the Second Search/Replace Transform:

    In the Search/Replace Transform Editor, select the PO_No column from the "Search Column" menu.
    In the "Search Value" field, enter the placeholder character you used in the first transform (e.g., #).
    In the "Replace With" field, leave it blank to remove the placeholder character.
    Here is how you can set it up:

    Search Value: #
    Replace With: (leave this field blank)

  • Roman_K
    Roman_K Member
    Answer ✓
    image.png
  • Roman_K
    Roman_K Member

    I was able to successfully remove the leading zero using the following:

    CASE
    WHEN INSTR(fd.PO_NO, ''0'') = 1 THEN SUBSTR(fd.PO_NO, 2)
    ELSE fd.PO_NO
    END AS PO_No,

Categories