CODEWITHSUNDEEP
sqlsql-servert-sql
Lloyd Thomas
Lloyd Thomas

Reputation: 356

How to use the SQL REPLACE Function, so that it will replace some text between a certain range, rather than one specific value

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.

Upvotes: 0

Views: 361

Answers (3)

Alan Burstein
Alan Burstein

Reputation: 7928

If you are always starting with "PIDLL", you can just remove the "PIDLL", cast the rest as an INT to lose the leading 0's, then append the front of the string with "PID/". One line of code.

-- Sample Data
DECLARE @t TABLE (ProductID VARCHAR(40));
INSERT @t VALUES('PIDLL0000074853'),('PIDLL000086752'),('PIDLL00000084276');

-- Solution
SELECT t.ProductID, NewProdID = 'PID/'+LEFT(CAST(REPLACE(t.ProductID,'PIDLL','') AS INT),20)
FROM   @t AS t;

Returns:

ProductID          NewProdID
------------------ ----------------
PIDLL0000074853    PID/74853
PIDLL000086752     PID/86752
PIDLL00000084276   PID/84276

Upvotes: 0

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22275

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 |
+----+------------------+-----------+

Upvotes: 1

Thom A
Thom A

Reputation: 95830

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);

Upvotes: 0

Related Questions