How to use the SQL REPLACE Function, so that it will replace some text between a certain range, rath-CodePudding

I have a table called Product and I am trying to replace some of the values in the Product ID column pictured below:

ProductID
PIDLL0000074853
PIDLL000086752
PIDLL00000084276

I am familiar with the REPLACE function and have used this like so:

SELECT REPLACE(ProductID, 'LL00000', '/') AS 'Product Code' 
FROM Product

Which returns:

Product Code
PID/74853
PIDLL000086752
PID/084276

There will always be there letter L in the ProductID twice LL. However, the zeros range between 4-6. The L and 0 should be replaced with a /.

If anyone could suggest the best way to achieve this, it would be greatly appreciate. I'm using Microsoft SQL Server, so standard SQL syntax would be ideal.

CodePudding user response：

This isn't particularly pretty in T-SQL, as it doesn't support regex or even pattern replacement. Therefore you method is to use things like CHARINDEX and PATINDEX to find the start and end positions and then replace (don't read REPLACE) that part of the text.

This uses CHARINDEX to find the 'LL', and then PATINDEX to find the first non '0' character after that position. As PATINDEX doesn't support a start position I have to use STUFF to remove the first characters.

Then, finally, we can use STUFF (again) to replace the length of characters with a single '/':

SELECT STUFF(V.ProductID,CI.I 2,ISNULL(PI.I,0),'/')
FROM (VALUES('PIDLL0000074853'),
            ('PIDLL000086752'),
            ('PIDLL00000084276'),
            ('PIDLL3246954384276'))V(ProductID)
     CROSS APPLY(VALUES(NULLIF(CHARINDEX('LL',V.ProductID),0)))CI(I)
     CROSS APPLY(VALUES(NULLIF(PATINDEX('%[^0]%',STUFF(V.ProductID,1,CI.I 2,'')),1)))PI(I);

CodePudding user response：

Please try the following solution.

All credit goes to @JeroenMostert

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, ProductID VARCHAR(50));
INSERT INTO @tbl (ProductID) VALUES
('PIDLL0000074853'),
('PIDLL000086752'),
('PIDLL00000084276'),
('PITLL0000084770');
-- DDL and sample data population, end

SELECT * 
    , CONCAT(LEFT(ProductID,3),'/', CONVERT(DECIMAL(38, 0), STUFF(ProductID, 1, 5, ''))) AS [After]
FROM @tbl;

Output

 ---- ------------------ ----------- 
| ID |    ProductID     |   After   |
 ---- ------------------ ----------- 
|  1 | PIDLL0000074853  | PID/74853 |
|  2 | PIDLL000086752   | PID/86752 |
|  3 | PIDLL00000084276 | PID/84276 |
|  4 | PITLL0000084770  | PIT/84770 |
 ---- ------------------ -----------