Removing leading zeros from PO_Number, (using OPENQUERY)

Roman_K

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,

GrantSmith

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

Roman_K

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